Microsoft KB Archive/263872

= INF: Restrictions for Use of the Fraction Field of a TIMESTAMP_STRUCT with SQL Server =

Article ID: 263872

Article Last Modified on 11/14/2003

-

APPLIES TO


 * 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 Q263872



SUMMARY
The Microsoft Developer Network (MSDN) documentation for the ODBC C data type, TIMESTAMP_STRUCT, states that the value of the fraction field can range from 0 to 999,999,999 to signify the number of billionths of a seconds or nanoseconds. However, if the fraction field is set to a value that represents a sub-millisecond and is then used to insert a date/time value into the datetime field of a SQL Server table, the insert fails with the following ODBC error message:

22008 [Microsoft][ODBC SQL Server Driver]Datetime field overflow

The error message indicates that an overflow condition of the SQL Server datetime data type occurred. This behavior is expected because the current accuracy of the datetime data type in SQL Server is millisecond (one three-hundredth second to be precise).



MORE INFORMATION
The following code demonstrates this problem: fraction = 500000000 /* Represents 500.0 ms - successful */ fraction = 500500000 /* Represents 500.5 ms - fail */ Therefore, a valid value for fraction is xxx000000, where 'xxx' ranges from 0 to 999. This must be accounted for in the ODBC C code where the sub-millisecond portion needs to be rounded or truncated prior to being inserted into a SQL Server datetime field.