SQL 从字符串转换日期和/或时间时转换失败，即使结果正确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21187581/
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
Conversion failed when converting date and/or time from character string even though result is correct
提问by neeko
This is my query:
这是我的查询：
set dateformat mdy;
select cast([File Date] as date) as 'test' from gpdetail
which produces this error:
产生此错误：
Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.
消息 241，级别 16，状态 1，第 2 行 从字符串转换日期和/或时间时转换失败。
Any idea how to get rid of the error? any help much appreciated have been stuck on this for ages :(
知道如何摆脱错误吗？任何非常感谢的帮助都被坚持了很长时间:(
However, Under the results tab it shows the correct results
但是，在结果选项卡下，它显示了正确的结果
This is the actual data
这是实际数据
回答by Gordon Linoff
The place to start is with the isdate()function:
开始的地方是isdate()函数：
select [File Date]
from gpdetail
where isdate([File Date]) = 0;
This may find places where the date does not conform.
这可能会找到日期不一致的地方。
If you just want to ignore badly formatted strings, then try:
如果您只想忽略格式错误的字符串，请尝试：
select (case when isdate([File Date]) = 1
then cast([File Date] as date)
end) as test
from gpdetail
