SQL cast datetime

In SQL Server 2005, why does:

PRINT Cast('' AS datetime)

display:

Jan 1 1900 12:00AM

I would have thought it should be null?开发者_JAVA技巧

It's because empty string '' is not NULL. If you do:

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 datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

The empty string is casted to 0 which is later casted to the era date.

Unlike Oracle, SQL Server distinguishes between NULL and an empty string.

From experimentation, it looks like SQL Server attempts to cast directly to DateTime, and failing that attempts to cast to int and then to 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.