INF: Restrictions for Use of the Fraction Field of a TIMESTAMP_STRUCT with SQL Server (263872)


The information in this article applies to:

  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
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.

REFERENCES

For more information on how the datetime data type is stored internally with SQL Server, see the SQL Server Books Online topic "Datetime and smalldatetime".

For the documentation of the ODBC C data types, see the following MSDN Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcc_data_types.asp

Modification Type:MajorLast Reviewed:11/14/2003
Keywords:kbinfo KB263872 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.