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.