SQL Server Datetime NULL value

Question

I want to insert values into a SQL Server table, but every time I get

"Cannot insert the value NULL into column 'ddod'" error.

[ddod] [datetime] NOT NULL

I'm not inserting NULL value, but 0000-00-00 00:00:00.000, because no datetime was chosen.

Is 0000-00-00 00:00:00.000 the same as NULL?

Thanks a lot for help

0000-00-00 00:00:00.000 is not a valid datetime and would fail anyway. How are you inserting this? — Martin Smith
– Martin Smith, Commented Oct 6, 2011 at 12:18
What is the valid zero datetime, I've something like this 1900-01-01 00:00:00.000. I'm using INSERT query. — peter.o
– peter.o, Commented Oct 6, 2011 at 12:21

Martin Smith · Accepted Answer · 2011-10-06 12:22:41Z

3

'0000-00-00 00:00:00.000' is not a valid datetime

select CAST('0000-00-00 00:00:00.000' as datetime)

gives the following error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Do you have the following options off?

set arithabort off
set ansi_warnings off

select CAST('0000-00-00 00:00:00.000' as datetime)

In that case it gets cast to NULL instead.

Is there any reason you are not using NULL anyway to represent the absence of a value rather than a "magic" sentinel value?

answered Oct 6, 2011 at 12:22

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

@PeterO - The minimum datetime is '1753-01-01 00:00:00.000'