24 January 2008 18:28
tonyrogerson
Why '2007-12-31T23:59:30' is actually '2008-01-01T00:00:00' - the dangers of implicit conversion
A collegue raised a good question today, why does using BETWEEN '2007-01-01' and '2007-12-31 23:59:59' return rows that are in the year 2008 and using BETWEEN '2007-01-01' and '2007-12-31 23:59:29' not?
The accuracy of smalldatetime is to the minute so when specifying seconds SQL Server will round up or round down to the nearest minute...
print cast( '2007-12-31 23:59:29' as datetime )
print cast( '2007-12-31 23:59:29' as smalldatetime )
Dec 31 2007 11:59PM
Dec 31 2007 11:59PM
print cast( '2007-12-31 23:59:30' as datetime )
print cast( '2007-12-31 23:59:30' as smalldatetime )
Dec 31 2007 11:59PM
Jan 1 2008 12:00AM
If you use DATEPART( second on the casted date you will see that on casting to smalldatetime you will get 0.
smalldatetime is often used when you are storing only the date portion or you are not interesting in seconds, this is to save space more than anything else.
When you query columns that are definied as smalldatetime for instance....
select * from trades where trade_date between '20070101' and '2007-12-31T23:59:59'
You need to make sure you don't specify the seconds otherwise SQL Server will round your input so the above query will actually become...
select * from trades where trade_date between '20070101' and '2008-01-01T00:00:00'
It's probably safer to use >= and < for instance...
select * from trades where trade_date >= '20070101' trade_date < '2008-01-01'
Another aspect - make sure you specify date literals without the hypens! If somebody changes your default langauge to British then '2007-10-01' will actually become 1st January 2007 and not 10th October 2007.
Filed under: SQL Server