SQL 转换日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2518099/
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 cast datetime
提问by Ian Vink
In SQL Server 2005, why does:
在 SQL Server 2005 中，为什么：
PRINT Cast('' AS datetime)
PRINT Cast('' AS datetime)
display:
展示：
Jan 1 1900 12:00AM
1900 年 1 月 1 日上午 12:00
I would have thought it should be null?
我会认为它应该是null？
采纳答案by Quassnoi
The empty string is casted to 0which is later casted to the era date.
空字符串被转换0为稍后转换为纪元日期。
Unlike Oracle, SQL Serverdistinguishes between NULLand an empty string.
与 不同Oracle，SQL Server区分NULL和 空字符串。
回答by KM.
It's because empty string ''is not NULL. If you do:
这是因为空字符串''不是NULL. 如果你这样做：
select Cast(null AS datetime)
OUTPUT:
输出：
-----------------------
NULL
(1 row(s) affected)
CAST and CONVERT (Transact-SQL)
When character data that represents only date or only time components is cast to the datetimeor smalldatetimedata types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.
当仅表示日期或仅表示时间分量的字符数据转换为datetime或smalldatetime数据类型时，未指定的时间分量设置为 00:00:00.000，未指定的日期分量设置为 1900-01-01。
回答by RedFilter
From experimentation, it looks like SQL Server attempts to cast directly to DateTime, and failing that attempts to cast to intand then to DateTime:
从实验来看，看起来 SQL Server 尝试直接转换为DateTime，并且尝试转换为int然后失败DateTime：
PRINT Cast('2009-1-1' AS datetime)
go
PRINT Cast('2009/1/1' AS datetime)
go
PRINT Cast('1.1' AS datetime)
go
PRINT Cast('1/2009/1' AS datetime)
go
PRINT Cast('' AS int)
go
PRINT Cast(' ' AS int)
go
PRINT Cast(0 AS datetime)
go
PRINT Cast('X' AS datetime)
go
PRINT Cast('X' AS int)
Output:
输出：
Jan 1 2009 12:00AM
Jan 1 2009 12:00AM
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Jan 1 2009 12:00AM
0
0
Jan 1 1900 12:00AM
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'X' to data type int.
