Microsoft KB Archive/281676

= PRB: Converting Datetime to Another Datatype May Result in Inaccurate Value =

Article ID: 281676

Article Last Modified on 10/16/2003

-

APPLIES TO


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

-



This article was previously published under Q281676



SYMPTOMS
When a datetime value is converted into another data type and then converted back into datetime, the datetime value returned may lose its accuracy due to the rounding during the conversion. The new value depends on the precision of the data type to which it was converted.



CAUSE
Values with the datetime data type are stored internally by SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Accurate conversion will occur only if the data type can store the precise time value.



WORKAROUND
There is no way to completely avoid loss of precision of the milliseconds due to the fact that the rounding must meet the ODBC specification. However, a numeric or decimal data type may have less loss of precision than other types.



MORE INFORMATION
The example below illustrates the potential problem as described in the following article in the Microsoft Knowledge Base:

135861 PRB: Datetime Rounding Up Behavior Undesirable

The date value passed is 2000-12-07 but the returned date is 2000-12-08. declare @var1 int, @var2 decimal(28,20), @date1 datetime, @Rdate1 datetime, @Rdate2 datetime

set @date1 = '2000-12-07 11:59:59.997 PM' set @var1 = convert(int,@date1) set @var2 = convert(decimal(28,20), @date1) set @RDate1 = convert(datetime, @var1) set @Rdate2 = convert(datetime, @var2)

select @date1 as 'Input Date', @Rdate1 as 'Int converted', @Rdate2 as 'Decimal converted'

Input Date                Int converted            Decimal converted ---  -     --- 2000-12-07 23:59:59.997   2000-12-08 00:00:00.000   2000-12-07 23:59:59.997 Additional information may be found in the SQL Server Books Online topics &quot;Datetime and smalldatetime&quot; and &quot;Converting datetime and smalldatetime&quot;

Additional query words: datetime convert integer int cast millisecond time date

Keywords: kbprb KB281676

-

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

© Microsoft Corporation. All rights reserved.