PRB: Datetime Rounding Up Behavior Undesirable

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.

   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