Q252699: BUG: Access ODBC Driver Cannot Insert Dates Prior to the Year 17

Article: Q252699
Product(s): Open Database Connectivity (ODBC)
Version(s): 2.1,2.5,2.6,4.0
Operating System(s): 
Keyword(s): kbDatabase kbJET kbODBC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC210SP2bug kbMDAC250bug
Last Modified: 24-AUG-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft ODBC Driver for Access, version 4.0 
- Microsoft Data Access Components versions 2.1, 2.5, 2.6 
-------------------------------------------------------------------------------

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
==========

This is a limitation of the Jet 4.0 ODBC driver. No workaround is available.

STATUS
======

Microsoft has confirmed this to be a bug in the Microsoft products listed at the
beginning of this article.

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          : kbDatabase kbJET kbODBC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC210SP2bug kbMDAC250bug kbMDAC260bug 
Technology        : kbAudDeveloper kbAccessSearch kbODBCSearch kbMDACSearch kbMDAC210 kbMDAC250 kbMDAC260 kbODBCAccess400
Version           : :2.1,2.5,2.6,4.0
Issue type        : kbbug
Solution Type     : kbpending

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.