PRB: Converting Datetime to Another Datatype May Result in Inaccurate Value (281676)

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 "Datetime and smalldatetime" and "Converting datetime and smalldatetime"