MS SQL Server datetime precision rounding effect

Microsoft SQL server has datetime data type to support date with time. JDBC timestamp sql type maps to datetime type in SQL server. JDBC timestamp extends java.util.Date and adds nanosecond precision.


java.util.Date has millisecond precision where as SQL server datetime has a precision of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds.


The issue with mapping JDBC timestamp to SQL server datetime is that there is a loss of precision when it gets stored to SQL server. So far example if your date in java is 01/01/98 23:59:59.999 it will get stored as 1998-01-02 00:00:00.000.

Here is the table from SQL server documentation which shows rounding effect of dates.

Java Date	SQL Server datetime rounded example
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, or 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 or 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

So a java date which is persisted in SQL server datetime field may have millisecond value which is  slightly different than the millisecond value of the original date. Care must me taken when comparing a date which is persisted in SQL server with original date. It is best to ignore millisecond when doing date comparison. For more details see SQL Server datetime rounding effect documentation.
Promote your blog