Article

About: Restrictions on the use of the ODBC datatype SQL_TYPE_TIMESTAMP

« Go Back

Information

 
Environment
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
Cause
Resolution


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.

Workaround
Notes
Attachment 
Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

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



Characters Remaining: 255

 



Copyright © 1993-2014. Progress Software Corporation. All Rights Reserved.