SQL Server datetime column losing a milliseconds

Introduction:

In this article I will describe that why is SQL Server datetime datatype column losing a millisecond or rounding?
In my example I try to insert ‘2013-08-26 12:45:37.991’ but it insert as ‘2013-08-26 12:45:37.990’.

Problem:

1
2
3
4
5
6
7
DECLARE @datetime datetime = '2013-08-26 12:45:37.991';
 
SELECT @datetime AS '@datetime'
--Result
--@datetime               	
------------------------- 
--1968-10-23 12:45:37.990

Here last millisecond round of from 991 to 990.

Reason:

Datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value System stored DateTime value
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995
01/01/98 23:59:59.996
01/01/98 23:59:59.997
01/01/98 23:59:59.998
1998-01-01 23:59:59.997
01/01/98 23:59:59.992
01/01/98 23:59:59.993
01/01/98 23:59:59.994
1998-01-01 23:59:59.993
01/01/98 23:59:59.990
01/01/98 23:59:59.991
1998-01-01 23:59:59.990

Solution:

Use DateTime2 with precision. Check below example

1
2
3
4
5
6
7
DECLARE @datetime2 datetime2(3) = '2013-08-26 12:45:37.991';
DECLARE @datetime datetime = @datetime2;
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';
--Result
--@datetime               	@datetime2
------------------------- 	------------------------
--2013-08-26 12:45:37.990	2013-08-26 12:45:37.991

Recommendation:

Always use DatTime2 in compare to dateTime. It has 2 great advantages:
1. DateTime2 with precision 3 takes 6 bytes where as datetime to takes 8 bytes.
2. DateTime2 never loose milliseconds
Its recommended by msdn to use datetime2 if you are looking for more seconds precision.

Reference:

Please read basic detail about DateTime, DateTime2 and datetimeoffset here
http://technet.microsoft.com/en-us/library/ms187819.aspx


Thanks!
Avinash

calendarSeptember 1, 2013 · cardInfoyen · commentsNo Comments
tagPosted in: MS SQL

Leave a Reply

Spam Protection: , required

myworldmaps infoyen