Empty string for non character datatypes

One of my friends asked me why an empty string for a datetime value is stored as 1900-01-01 00:00:00.000. Well you should not pass an empty string to a non- character data type because depends on the datatype SQL Server internally convert empty string and store it differently.

Let us consider the following example

declare @test table (int_col int, datetime_col datetime, float_col float,money_col money)
insert into @test (int_col, datetime_col, float_col,money_col)
select '','','',''
select * from @test

The result is

int_col datetime_col           float_col   money_col
------- ---------------------- ---------   -----------
0       1900-01-01 00:00:00.000   0         0.00

All empty strings are actually 0 and for datetime datatype 0 is Jan 01, 1900

select cast(0 as datetime)

results to

1900-01-01 00:00:00.000


My point is that you should not pass empty string to non-character datatype. If you want you need to set it to NULL. Passing empty string will result to default value of 0 in SQL Server and probably will throw error in other RDBMSs. I also think that SQL Server should throw an error in this case


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s