转换 SQL Server 空日期/时间字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1201541/
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
Converting SQL Server null date/time fields
提问by kenalacom
Whenever the value is null for this query
每当此查询的值为 null 时
SELECT ISNULL(someDateTime,'')
FROM someTable
the result is
结果是
someDateTime ------------ 1900-01-01 00:00:00.000
I want it to be "No", so if I run this:
我希望它是“否”，所以如果我运行这个：
SELECT ISNULL(someDateTime,'No')
FROM someTable
then there's this error:
然后有这个错误：
Conversion failed when converting datetime from character string.
从字符串转换日期时间时转换失败。
How to do it? Thanks in advance!
怎么做？提前致谢！
回答by Michael Petrotta
The result of the expression will need to be a single type. If you want a character string (and you do, since 'No' is not a DateTime), you'll need to convert the datetime to such a string:
表达式的结果将需要是单一类型。如果您想要一个字符串（并且您确实需要，因为 'No' 不是 DateTime），则需要将日期时间转换为这样的字符串：
SELECT ISNULL(cast(someDatetime as varchar(20)), 'No') FROM someTable
As others have suggested, though, code like this smells bad, and you may want to pass the null to a client component and do the conversion there.
但是，正如其他人所建议的那样，像这样的代码闻起来很糟糕，您可能希望将 null 传递给客户端组件并在那里进行转换。
回答by Andrew Hare
isnull()is trying to convert the second argument to the datatype of the field you specify in the first argument.
isnull()正在尝试将第二个参数转换为您在第一个参数中指定的字段的数据类型。
If you are going to be returning a string you need to cast the DateTimefield to a string type so that isnull()can work properly - see Michael Petrotta'sanswer for a way to accomplish this.
如果您要返回一个字符串，您需要将该DateTime字段转换为字符串类型，以便isnull()可以正常工作 - 请参阅Michael Petrotta 的回答以了解实现此目的的方法。
回答by Joel Coehoorn
You're still selecting a DateTime column, and so the result of that expression still needs to be a DateTime value rather than a string. To suggest an appropriate work-around, we'll need to know more about what you're really trying to do with this data.
您仍在选择 DateTime 列，因此该表达式的结果仍然需要是 DateTime 值而不是字符串。为了提出适当的解决方法，我们需要更多地了解您真正想用这些数据做什么。
回答by gbn
You can't as such directly. It's easier to trap NULL in the client and change it to "no" there.
你不能直接这样。在客户端捕获 NULL 并将其更改为“no”更容易。
However, you could use a token value such as "17530101" which is a valid datetime, or CONVERT SomeDateTime first to varchar.
但是，您可以使用一个令牌值，例如“17530101”，它是一个有效的日期时间，或者首先将 SomeDateTime 转换为 varchar。
Otherwise, we need more info on why etc
否则，我们需要更多关于原因等的信息
回答by HarryKruse
In your Update Stored Proc: Update your previous date value to a new Null value:
在您的更新存储过程中：将您以前的日期值更新为新的 Null 值：
.Parameters.AddWithValue("@Date_Value", Nothing).IsNullable = True
