SQL date type range not according to spec?

Question

According to MSDN the range for date datatype is January 1, 1753, through December 31, 9999

In SQL2005 the following is interesting:

declare @date as datetime
SET @date = '1/1/1901 12:00:00 AM'
SELECT @date

if (isnull(@date,0) = 0) SELECT 'date is null' ELSE SELECT 'date not null'

change the date to '1/1/1901 12:00:00 AM' and suddenly isnull(@date,0) is 0.

What am I missing?

(Edit) fixed daterange for sql2005, link to sql2005 BOL

Answer 1

The line of code below is misleading:

if (isnull(@date,null) = 0) SELECT 'date is null' ELSE SELECT 'date not null'

This line returns 0 if @date == 0, not when it is null. The date you've specified is stored as 0 in a datetime.

SELECT convert(datetime, 0)

to see what I mean!

Answer 2

declare @date as datetime
SET @date = '1/1/1901 12:00:00 AM'
SELECT @date

if @date is null SELECT 'date is null' ELSE SELECT 'date not null'

ISNULL() is a conversion function. It is not the check if a value is NULL.

A date has a numeric representation. The numeric representation of '1/1/1901 12:00:00 AM' happens to be 0, on SQL Server.

You can't compare the result of ISNULL(@date,0) to 0 without losing the ability to distinguish between a date that could be represented as 0 and a date that is NULL.

Answer 3

Try this:

declare @date as datetime
SET @date = '1/1/1901 12:00:00 AM'

SELECT @date, CASE WHEN @date IS NULL THEN 'date is null' ELSE 'date not null' END

Answer 4

I tried this:

declare @date as datetime
SET @date = '1/1/1901 12:00:00 AM'
SELECT @date, CONVERT(numeric(9,3), @date) as DateValue
if (isnull(@date,0) = 0) SELECT 'date is zero' ELSE SELECT 'date not equal to zero (THIS PRINTS)'
if (isnull(@date,0) = 365) SELECT 'date is 365 (THIS PRINTS)' ELSE SELECT 'date not equal to zero'
if (@date = 365) SELECT 'date is 365 (THIS PRINTS)' ELSE SELECT 'date not 365'

SET @date = '1/1/1900 12:00:00 AM'
SELECT @date, CONVERT(numeric(9,3), @date) as DateValue
if (isnull(@date,0) = 0) SELECT 'date is equal to Zero (THIS PRINTS)' ELSE SELECT 'date not null'
if (@date = 0) SELECT 'date is equal to Zero (THIS PRINTS)' ELSE SELECT 'date not null'

There is nothing wrong with ISNULL nor with COALESCE. The problem is that you are comparing with zero, and you fool yourself into thinking that the ISNULL kicked in. Try my code, and see that ISNULL returns the correct value.