Microsoft KB Archive/135861

= PRB: Datetime Rounding Up Behavior Undesirable =

Article ID: 135861

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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

Additional query words: sql6 select null

Keywords: kbother kbprb KB135861

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.