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.

Steps to Reproduce
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.

Last Modified Date9/14/2015 6:42 AM

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Was this article helpful?


Your feedback is appreciated.

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

Characters Remaining: 255