PRB: Datetime Rounding Up Behavior Undesirable (135861)


The information in this article applies to:

  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q135861

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.

RESOLUTION

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

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
Modification Type:MinorLast Reviewed:3/14/2005
Keywords:kbother kbprb KB135861
©2004 Microsoft Corporation. All rights reserved.