Microsoft KB Archive/252699

Knowledge Base

FIX: Access ODBC driver can not insert dates prior to the year 1753

Article ID: 252699

Article Last Modified on 1/5/2007

---

APPLIES TO

• Microsoft Open Database Connectivity Driver for Access 4.0
• Microsoft Data Access Components 2.1
• Microsoft Data Access Components 2.5
• Microsoft Data Access Components 2.6

---

This article was previously published under Q252699

SYMPTOMS

When you insert a date prior to the year 1753 by using Microsoft Access ODBC Driver, the following error message is displayed:

[22008] [Microsoft][ODBC Microsoft Access Driver]Datetime field overflow (null)

RESOLUTION

To resolve this problem, apply the hotfix that is described in the following Microsoft Knowledge Base article:

264081 FIX: Incorrect pre-1753 date values with Jet ODBC driver

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to Reproduce the Behavior

1. Create a new Microsoft Access 97 or Microsoft 2000 database.
2. Create a new Microsoft Visual C++ Win32 Console application.

3. Copy the following code into the application:

   #include <windows.h>
   #include <sql.h>
   #include <sqlext.h>
   #include <tchar.h>
   #include <stdlib.h>
   #include <stdio.h>
   
   
   void HandleError(SQLHANDLE  hHandle, SQLSMALLINT hType, RETCODE RetCode)
   {
       SQLSMALLINT iRec = 0;
       SQLINTEGER  iError;
       TCHAR       szMessage[1000];
       TCHAR       szState[SQL_SQLSTATE_SIZE];
   
   
       if (RetCode == SQL_INVALID_HANDLE)
       {
           fprintf(stderr,"Invalid handle!\n");
           return;
       }
   
   
       while (SQLGetDiagRec(hType,
                            hHandle,
                            ++iRec,
                            (SQLCHAR *)szState,
                            &iError,
                            (SQLCHAR *)szMessage,
                            (SQLSMALLINT)(sizeof(szMessage) / sizeof(TCHAR)),
                            (SQLSMALLINT *)NULL) == SQL_SUCCESS)
       {
           fprintf(stderr,TEXT("[%5.5s] %s (%d)\n"),szState,szMessage,iError);
       }
   
   }
   
   void main(int argc, char* argv[])
   {
       SQLHENV henv;
       SQLHDBC hdbc;
       SQLHSTMT hstmt;
       SQLRETURN nstatus;
       
       SQLCHAR szConnect[1024];
       SQLSMALLINT cbConnString;
   
       //Not checking the return codes in some cases for clarity.
       
       nstatus = SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);
       nstatus = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
       nstatus = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
       nstatus = SQLDriverConnect(hdbc,NULL,
           (SQLCHAR*) "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\db1.mdb", 
           SQL_NTS, szConnect, 1024, &cbConnString, SQL_DRIVER_NOPROMPT);
           
       if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO)
       {
           HandleError(hdbc,SQL_HANDLE_DBC,nstatus);
           return;
       }
   
       nstatus = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
   
       nstatus = SQLExecDirect(hstmt,(SQLCHAR*) "CREATE TABLE DateTable (dateval datetime)",SQL_NTS);                              
       if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO)
       {
           HandleError(hstmt,SQL_HANDLE_STMT,nstatus);
           return;
       }
   
       TIMESTAMP_STRUCT dateVal;
       memset(&dateVal,0,sizeof(TIMESTAMP_STRUCT));
       dateVal.year = 1750;
       dateVal.month = 1;
       dateVal.day = 1;
   
       nstatus = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP,
           0,0,(SQLPOINTER*) &dateVal,0,0); 
   
       nstatus = SQLExecDirect(hstmt, (SQLCHAR*) "INSERT INTO DateTable VALUES (?)",SQL_NTS);
       if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO)
       {
           HandleError(hstmt,SQL_HANDLE_STMT,nstatus);
       }
   
       nstatus = SQLExecDirect(hstmt,(SQLCHAR*) "DROP TABLE DateTable",SQL_NTS);                               
       if (nstatus != SQL_SUCCESS && nstatus != SQL_SUCCESS_WITH_INFO)
       {
           HandleError(hstmt,SQL_HANDLE_STMT,nstatus);
       }
   
   
       nstatus = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
       nstatus = SQLDisconnect(hdbc);
       nstatus = SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
       nstatus = SQLFreeHandle(SQL_HANDLE_ENV,henv);
   
       printf("Done");
   }
                           

4. Change the connection string to reflect the location of your Access database.
5. Compile and run the application. Observe errors.

Additional query words: datetime incorrect insert over flow 1753 1754 1800

Keywords: kbbug kbdatabase kbjet kbpending KB252699

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.