Recently I come across with one very interesting observation. When you assign any date in datetime variable with milliseconds and print it, you will get difference of few milliseconds, sometime one or two or three. I don’t know whether SQL Server is rounding the datetime or it is a bug or SQL Server is not so much accurate up to milliseconds. Have a look at below examples:
declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.998'
select @datetest --answer is 2009-01-01 23:59:59.997
GO
declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.992'
select @datetest --answer is 2009-01-01 23:59:59.993
GO
declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.982'
select @datetest --answer is 2009-01-01 23:59:59.983
GO
declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.999'
select @datetest --answer is 2009-01-02 00:00:00.000
GO
I tried to find exact reason for this but wasn’t able to do so.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
4 comments:
Hi Ritesh,
This is expected behavior of SQL Server DATETIME datatype. Same kind of accuracy is available in SQL Server 2000 and SQL Server 2005.
SQL Server 2008 Datatype DATETIME2 which is much larger accuracy.
Run above example again as following change.
DECLARE @datetest DATETIME2
SET @datetest = '2009-01-01 23:59:59.998'
SELECT @datetest --answer is 2009-01-01 23:59:59.9980000
GO
Kind Regards,
Pinal
One more thing,
SQL Server DATETIMEhas precision of 3 millisecond.
Just like SMALLDATETIME has precision of 1 min.
Kind Regards,
Pinal
Hi Pinal,
Thank you very much for your response. I really appreciate it. BTW, does it mean that DateTime data type is not accurate up to milliseconds?
Yes,
DATETIME has precision of only 3 milisecond.
Post a Comment