IsNull and Dates

Posted: May 10, 2010 in TSQL

The following code was written expecting the results to be -1,-1

DECLARE @x as datetime;
set @x = NULL;
SELECT IsNull(@x,-1) AS Col_1, IsNull(NULL,-1) AS Col_2;

The actual result was 1899-12-31 00:00:00.000,-1

Why did it happen?

The reason it does this is because dates can be given INT values where 0 = 1900-01-01

All other dates are relative to that so:
1 = 1900-01-02
-1 = 1899-12-31

SELECT IsNull(@x,-1) AS Col_1;
Is the same as set @x = -1;
Which is the same as
set @x = ‘1899-12-31 00:00:00.000’;

So it did assign the value -1 to the date which then translated to Dec 31st 1899


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s