SQL cast datetime

Question

In SQL Server 2005, why does:

PRINT Cast('' AS datetime)

display:

Jan 1 1900 12:00AM

I would have thought it should be null?

Answer 1

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.

Answer 2

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.