Sql Cast Datetime

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.

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.