SQL Server：使用 case 语句查看日期是否为 NULL 以及它是否返回“ ”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3292608/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: Using a case statement to see if a date is NULL and if it is returning ' '
提问by user380432
I have a column in my select statement that looks like this:
我的 select 语句中有一个列，如下所示：
SELECT CASE WHEN fu.SentOutDate IS NULL THEN '' ELSE fu.SentOutDate END
This returns 1900-01-01 00:00:00.000for the ones that would otherwise be NULL
这将返回1900-01-01 00:00:00.000否则为 NULL 的那些
I know this because when I put in just fu.SentOutDateit comes up as NULL
我知道这一点，因为当我输入时，fu.SentOutDate它会出现NULL
Why does this happen and how can I just get it to return a blank value?
为什么会发生这种情况，我怎样才能让它返回一个空白值？
回答by Fosco
Try converting the date to a string so it doesn't try to convert '' to a date:
尝试将日期转换为字符串，这样它就不会尝试将 '' 转换为日期：
(CASE WHEN fu.SentOutDate IS NULL THEN '' ELSE CONVERT(varchar,fu.SentOutDate) END)
回答by Mike M.
It's casting your '' to a DATETIME, since your other column you'd return is a datetime column.
它将您的 '' 转换为 DATETIME，因为您返回的另一列是日期时间列。
SELECT CASE WHEN 1=1 THEN '' ELSE GETDATE() END
will give you the same value...
会给你同样的价值......
You can convert this to a varchar(32), but I'm not certain of the ramifications
您可以将其转换为 varchar(32)，但我不确定后果
SELECT CASE WHEN 1=1 THEN '' ELSE CAST(GETDATE() AS varchar(32)) END
回答by crosan
If you're just checking for NULL values, you might try ISNULL() and cast the date as a varchar.
如果您只是检查 NULL 值，您可以尝试 ISNULL() 并将日期转换为 varchar。
SELECT ISNULL(CAST(fu.SentOutDate AS VARCHAR(50)), '') AS SendOutDate FROM tablename
SELECT ISNULL(CAST(fu.SentOutDate AS VARCHAR(50)), '') AS SendOutDate FROM tablename
回答by OMG Ponies
A column can only return one data type - DATETIME != string/VARCHAR.
一列只能返回一种数据类型 - DATETIME != string/VARCHAR。
If you want a zero length string in the event of the value being NULL, you have to explicitly change the data type, using CAST/CONVERTto change the non-NULL value to a VARCHAR/etc data type.
如果在值为 NULL 的情况下想要零长度字符串，则必须显式更改数据类型，使用CAST/CONVERT将非 NULL 值更改为 VARCHAR/etc 数据类型。
回答by ErikE
It sounds like you're displaying this value in a GUI or client somewhere. In my opinion, the best practice is to convert it from the NULL value there, not in the query.
听起来您正在某个地方的 GUI 或客户端中显示此值。在我看来，最佳实践是将其从 NULL 值转换为那里，而不是在查询中。
If you ever create a database that scales to millions of users, you want a little processing as possible in the database and as much as possible in the client. Doing conversion of a date to character is an unneeded load on the system (character calculation is always much slower than math).
如果您曾经创建过一个可扩展到数百万用户的数据库，您希望在数据库中进行尽可能少的处理，并在客户端中进行尽可能多的处理。将日期转换为字符是系统不必要的负载（字符计算总是比数学慢得多）。
回答by srk
This may work:
这可能有效：
case when ISNULL(convert(varchar, a.rec_dt, 108), '00:00:00')='00:00:00' then ''
else CAST(rec_dt as varchar)
