FIX: Error Message "Invalid Time Format" When You Insert Date/Time With Maximum Values

Article translations Article translations
Article ID: 259691 - View products that this article applies to.
This article was previously published under Q259691
Expand all | Collapse all

On This Page

SYMPTOMS

When you use the Microsoft SQL Server ODBC driver and you use SQLSetPos to insert or update date or time values that contain the maximum allowable values for the hour, minute, second and millisecond portions (such as 1999-10-03 23:59:59:999), the insert statement succeeds but incorrect values are saved to the database.

If you attempt to reselect the date/time values back, the statement either generates an error or returns incorrect values, depending on how the date/time field is bound. If the field is bound as a SQL_C_TIMESTAMP, the error message that follows occurs:
SQLState = 22007
[Microsoft][ODBC SQL Server ODBC Driver]Invalid time format
When the field is bound as a SQL_C_CHAR, no error occurs; however, incorrect values are returned, such as 1999-10-03 24:00:00.000. This can generate errors in the receiving application because 24:00:00.000 is not a valid date/time value.

This error occurs with the Windows 2000/MDAC 2.5 build of the SQL Server ODBC driver (version 3.70.820) and earlier.

CAUSE

An error in the algorithm that converts the date/time is not rolling the hours portion of the time over to the days portion of the date.

RESOLUTION

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next Microsoft Data Access Components service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support
NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.



The English version of this fix should have the following file attributes or later:

   Date       Version      Size             File name    
   -----------------------------------------------------

   04/04/00   3.70.0784     24,848 bytes    Odbcbcp.dll
   04/04/00   3.70.0784    516,368 bytes    Sqlsrv32.dll

				




Workaround

Here are two potential workarounds for this problem:
  • If possible, perform the inserts and updates through direct SQL statements, which then lets SQL Server handle the conversion of the date/time values.

    -or-

  • If storage of milliseconds is not necessary, define the field as a smalldatetime datatype instead of datetime. The smalldatetime datatype only uses 4 bytes for storage, and does not include millisecond precision.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Manual Installation of the Hotfix File

  1. Quit any applications or services that use the SQL Server ODBC driver, Sqlsrv32.dll. This may include Internet Information Server (IIS), Microsoft Transaction Server (MTS), and any ODBC, Microsoft ActiveX Data Objects (ADO), or OLE DB applications.
  2. Download the hotfix into a temporary directory.
  3. Locate and rename the current versions of the Sqlsrv32.dll and Odbcbcp.dll files, which should be in the \Winnt\System32 folder for computers running Microsoft Windows NT, and in the \Windows\System folder for computers running Microsoft Windows 95 and Microsoft Windows 98.
  4. Copy the hotfix files into the same location, and then restart your services and applications.

Steps to Reproduce Behavior

  1. Copy the code in step 2 into a Microsoft Visual C++ console application, and then compile the code. Please note that you may need to change the datasource name, user id and password.
  2. Run the code, and note that an error occurs when binding the date/time value as SQL_C_TIMESTAMP, and that an invalid hour value is returned when binding the date/time value as SQL_C_CHAR.
    #include <windows.h>
    #include <stdio.h>
    #include <sql.h>
    #include <sqlext.h>
    #include <time.h>
    
    void CreateTable(HSTMT);
    
    main()
    {
    
    //	ODBC handles
    	RETCODE rc;
    	HENV henv;
    	HDBC hdbc;
    	HSTMT hstmt;
    
    //	Variables for SQLConnect
    
    	char * dsn = "Pubs";
    	char * uid = "sa";
    	char * pwd = "";
    
    //	Variables for SQLDiagRec
    	char mstate[6] = "\0";
    	long native = 0;
    	char mtext[300] = "\0";
    	short mlength = 0;
    	short i = 0;
    	
    //	Variables for SQLBindCol
    	int m_ID = 1;
    	long m_idLen = 4;
    	TIMESTAMP_STRUCT m_time = {1999,10,03,23,59,59,999000000};
    	char m_timeChar[30] = "\0";
    	long m_timeLen = 16;
    	long sqlnts = SQL_NTS;
    
    //	miscellaneous variables	
    	char * strSQL = "Select * from MillisecTest";
    	char * strDropTable = "Drop table MillisecTest";
    	unsigned short status = 0;
    	unsigned long rowcount = 0;
    
    
    
    //	Allocate ODBC handles and connect
    	rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
    	rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,0);
    	rc = SQLAllocHandle(SQL_HANDLE_DBC,henv, &hdbc);
    
    	rc = SQLConnect(hdbc, (unsigned char *)dsn,
    		SQL_NTS, (unsigned char *)uid,
    		SQL_NTS, (unsigned char *)pwd, SQL_NTS);
    
    	rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    
    //	Table creation 
    	CreateTable(hstmt);
    
    //	Set statement attributes so SQLSetPos can be used
    	rc = SQLSetStmtAttr( hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_LOCK, 0 );
    	rc = SQLSetStmtAttr( hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0 );
    
    //	Bind the columns, using SQL_C_TIMESTAMP
    	rc = SQLBindCol(hstmt, 1, SQL_C_SHORT, &m_ID, 4, &m_idLen);
    	rc = SQLBindCol(hstmt, 2, SQL_C_TIMESTAMP, &m_time, sizeof(m_time), &m_timeLen);
    
    //	Execute the statement and use SQLSetPos to insert the date/time data
    	rc = SQLExecDirect(hstmt, (unsigned char *)strSQL, SQL_NTS);
    	rc = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 1, &rowcount, &status );
    	rc = SQLSetPos(hstmt, 1, SQL_ADD, SQL_LOCK_NO_CHANGE);
    
    
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    
    
    	memset(&m_time, 0, sizeof(m_time));
    
    //	Re-execute the statement, and attempt to fetch back the new date/time value
    	rc = SQLExecDirect(hstmt, (unsigned char *)strSQL, SQL_NTS);
    	rc = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 1, &rowcount, &status );
    
    	SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (unsigned char *)&mstate, &native, 
    				(unsigned char *)&mtext, 300, &mlength);
    	printf("\nError when attempting to bind as SQL_C_TIMESTAMP:\n\n");
    	printf("\tSQLSTATE\t%s\n\tNative Error\t%i\n\tMessage\t%s\n\n",mstate, native, mtext);
    
    
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    
    
    //	Bind the date/time column as SQL_C_CHAR this time
    	rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, m_timeChar, sizeof(m_timeChar), &sqlnts);
    	rc = SQLExecDirect(hstmt, (unsigned char *)strSQL, SQL_NTS);
    	rc = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 1, &rowcount, &status );
    	printf("\nInvalid hour when timestamp is bound as SQL_C_CHAR:  %s\n\n", m_timeChar); 
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    	
    
    
    //	Cleanup
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    	SQLExecDirect(hstmt, (unsigned char *)strDropTable, SQL_NTS);
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    	SQLFreeStmt(hstmt, SQL_DROP);
    	SQLDisconnect(hdbc);
    	SQLFreeConnect(hdbc);
    	SQLFreeEnv(henv);
    
    	printf("\nDo the \"Press any key\" thing...");
    	getchar();
    	return(TRUE);
    };
    
    //------- CreateTable() ----------------
    
    void CreateTable(HSTMT hstmt)
    {
    
    	RETCODE rc = 0;
    	char SqlStatements[2][90] = 
    	{"Drop table MillisecTest",
    	"Create table MillisecTest (ID integer constraint index1 PRIMARY KEY, TimeTest datetime)"};
    
    	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[0], SQL_NTS);
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[1], SQL_NTS);
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    
    
    }
    					

Properties

Article ID: 259691 - Last Review: October 12, 2005 - Revision: 4.4
APPLIES TO
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft ODBC Driver for Microsoft SQL Server 3.7
Keywords: 
kbhotfixserver kbqfe kbbug kbcodesnippet kbdatabase kbfix kbmdac210sp2fix kbqfe KB259691

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com