BUG: Datetime Rounding Up Behavior Undesirable

• Microsoft SQL Server, version 6.0

SYMPTOMS

In Microsoft SQL Server version 6.0, 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.

WORKAROUND

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

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

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