About: Restrictions on the use of the ODBC datatype SQL_TYPE_TIMESTAMP

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.

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.