0

There seems to be lots of posts on how to convert 1900-01-01 to NULL, but my problem is the opposite. I have stored procedures processing smalldatetime (SQL Server 2008), but they keep converting 1900-01-01 to NULL, when I really want it to remain as 1900-01-01. Any thoughts?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KirstieBallance
  • 1,238
  • 12
  • 26

1 Answers1

0

Fundamentally, SQL Server can't differentiate.

1900-01-01 00:00:00.000 is the epoch (zero point) for SQL Server's calendar. And if you say something like this in SQL Server:

select 'empty string',convert(datetime,'')

it's conversion algorithm doesn't throw an error, it converts the empty string to the SQL Server date/time value ... (you guessed it!) ... '1900-01-01 00.00.00.000'.

What happens with null values is dependent on the current setting of the SQL Server SET option SET CONCAT_NULL_YIELDS_NULL. If off, null values are essentially treated identically to empty strings.

As far as your stored procedure problem goes (stored procedure turning a date/time value of 1900-01-01 into null...that's a problem with your stored procedures. SQL Server won't automatically convert the non-null date/time value of 1900-01-01 00.00.00.000 to null: somebody is doing it explicitly.

And old versions of SQL Server tended to treat null and the empty string pretty much interchangably by default. I suspect your system has been around a long time.

As a result, SQL Server tends to have a hard time distinguishing an actual, non-null date/time value of 1900-01-01 00.00.00.000 from '' and null

My suspicion, since you haven't shown us any code, is that your stored procedure has code somewhere that looks something like:

select some_date_time_column = case when t.some_date_time_column == '' then null else t.some_date_time_column end
from some_table t
Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135