My Software Notes

Useful things I discover

Archive for March 2010

The Danger of Implicit Data Type Conversions in SQL Server

leave a comment »

I have never before hit the problem that I discovered today because, call me crazy but, I always store dates in a datetime column and not in varchar columns and I rarely use implicit conversions. However, today one of my coworkers was moving some data from an old table that was all varchar columns into a new table that actually defined the column using the data type of the data that was to go into the column (imagine that).

He started getting the date “1900-01-01 00:00:00 000” appearing in the new table and couldn’t figure out why. He asked me to help, so we looked at default constraints- no, triggers – no. Then I had him look at the source data and we saw a lot of empty values in the result of the select query. Then we realized that it wasn’t saying “null” in the result but nothing at all. So we added a “where len(rtrim(ddate)) = 0″ and sure enough the dates were either empty strings or contained spaces and SQL Server was converting them to ‘1900-01-01 00:00:00 000” (the earliest date of the smalldatetime data type – another WTF because the column was a datetime not a smalldatetime).

At first this made no sense to me. The documentation clearly says “When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times.“.  So I decided to play around and I think I figured it out.

SQL Server will implicitly convert a binary value to a datetime.  So if you give it an empty string (and this is my guess) it tries to convert it and fails, so then it tries to use the empty string as a binary and it succeeds, because the empty string has a binary value of zero and that equates to the date “1900-01-01 00:00:00 000”.  

If you give it spaces, SQL Server will trim the spaces and then attempt to do the conversion, so again, it ends up as an empty string.  (Try inserting ‘   1/1/2000  ‘ and you’ll see that SQL Server will convert it correctly despite the spaces, therefore I assume it trims them before attempting the conversion.)

Just try these nonsensical implicit conversions:
create table #tempTest ( Id int null, dtColumn datetime null )
go

insert into #tempTest (Id, dtColumn)
values (1, '')
go

insert into #tempTest (Id, dtColumn)
values (2, 0x0)
go

insert into #tempTest (Id, dtColumn)
values (3, 11)
go

select * from #tempTest
go

select cast(dtColumn as binary) as dtColumnAsBinary from #tempTest
go

Results:

Id          dtColumn
----------- -----------------------
1           1900-01-01 00:00:00.000
2           1900-01-01 00:00:00.000
3           1900-01-12 00:00:00.000

dtColumnAsBinary
--------------------------------------------------------------
0x000000000000000000000000000000000000000000000000000000000000
0x000000000000000000000000000000000000000000000000000000000000
0x000000000000000000000000000000000000000000000000000B00000000

So next time you consider just letting SQL Server implicitly convert your data – think again. You really have no idea what is going to come out the other end.

As a final note, I’ve been using SQL Server since version 1.1 (I guess that gives away my age) and I never hit problems like this before. I think it’s because I rarely use implicit conversions when I’m moving data around and I set a column’s data type to be the type of the data that will be going into it. It’s interesting how these simple practices have insulated me from problems like this for twenty years.

References:

Full list of implicit and explicit conversions supported by SQL Server (scroll down a bit)

All about Data Type Conversion in SQL Server

Written by gsdwriter

March 19, 2010 at 11:31 am

Posted in Database