About: Restrictions on the use of the ODBC datatype SQL_TYPE_TIMESTAMP

« Go Back


Question/Problem Description

About: Restrictions on the use of the ODBC datatype SQL_TYPE_TIMESTAMP

SQL_TYPE_TIMESTAMP is an ODBC datatype primarily used when a column of data in a database needs to contain exact information as to the time when something occurred, like a network disconnection, an error, or a when a process is started or stopped. As such, there are restrictions on what kind of data can be inserted into a column of datatype SQL_TYPE_TIMESTAMP.

For example, let's create a table using the ODBC driver for SQL Server and create a table with a column type that maps to SQL_TYPE_TIMESTAMP that might log ATM transactions:

create table ATM (AcctNo numeric (10,0), TransDate datetime, TransType char(5), TransAmt money)

According to page 1209 the ODBC 3.0 Programmer?s Reference, SQL_TYPE_TIMESTAMP, ?must conform to the constraints of the Gregorian calendar?. First, let?s insert a valid row of data:

insert into ATM values (123456789, '2001-01-01 12:00:00', 'WITH',10.00)

Now, if you attempt to insert a row with an invalid day, like Feburary the 30th, you will get an error like the following:

insert into ATM values (123456789, '2001-02-30 12:00:00', 'WITH',10.00)

Errors: '[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.'

You may also note, however, that a perfectly legitimate day, like the birthdate of Johann Sebastian Bach (March 21, 1685) will also fail with the same error messages.

Clarifying Information
Error Message
Defect/Enhancement Number

Dates prior to January 1, 1753 are not valid values for SQL_TYPE_TIMESTAMP.

Presumably, since databases were not kept on computers until the 20th century, there is no need to have a database capable of storing a timestamp value of an event happening before this time. SQL_TYPE_TIMESTAMP is meant to be used to note when a record is stored to the database, and as such, indicating that a record was entered prior to when computers were invented makes no sense. As such, you will note that dates prior to January 1, 1753 are not acceptable values for SQL_TYPE_TIMESTAMP.

It is not clear why 1752/1753 is the transition between unacceptable/acceptable SQL_TYPE_TIMESTAMP values. The best answer might lie in the fact that around this time, the Gregorian Calendar was adopted by a number of European nations as the standard solar-based calendar.

Was this article helpful?


Your feedback is appreciated.

Please tell us how we can make this article more useful.

Characters Remaining: 255