PRB: Datetime Rounding Up Behavior Undesirable

View products that this article applies to.

This article was previously published under Q135861

Expand all | Collapse all

SYMPTOMS

In Microsoft SQL Server version 6.0 and later, if the datetime field has a milliseconds entry of 999, it is stored as 000 milliseconds with the seconds value incremented by one. For example, a value of '08/22/1995 10:15:19:999' is stored as '08/22/1995 10:15:20:000'. This rounding up of 999 milliseconds has an undesirable effect if the time is '23:59:59:999' in that in moves the date over to the next date. Thus, a value of '12/31/1995 23:59:59.999' is stored as '01/01/1996 12:00:00:000'.

SQL Server version 4.21a stores 999 milliseconds as 996 milliseconds which is more acceptable because the date (mm/dd/yyyy) part remains the same.

Back to the top

RESOLUTION

Use milliseconds entries zero through 998 for the datetime field if date and year correctness is important.

Back to the top

MORE INFORMATION

This change was made intentionally in SQL Server 6.0 so that the rounding performed by the server was the same as the ODBC-specified behavior. Without this change, it was possible to receive two different answers for the same query depending on whether the SQL Server or the ODBC driver performed the rounding.

The following example replicates this problem:

   use pubs
   go
   create table testtime (x datetime)
   go
   insert into testtime values ('12/31/1995 23:59:59:999')
   go
   select convert (char, x, 109) from testtime
   go

Back to the top