CODEWITHSUNDEEP
sql-serverdatetimenull
peter.o
peter.o

Reputation: 3530

SQL Server Datetime NULL value

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

Upvotes: 1

Views: 8192

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

'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?

Upvotes: 3

Related Questions