Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


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.

↑ Back to the top


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: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.

↑ Back to the top


Status

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

↑ Back to the top


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);
    
    
    }
    					

↑ Back to the top


Keywords: kbhotfixserver, kbbug, kbcodesnippet, kbdatabase, kbfix, kbmdac210sp2fix, kbqfe, kbqfe, KB259691

↑ Back to the top

Article Info
Article ID : 259691
Revision : 8
Created on : 10/12/2005
Published on : 10/12/2005
Exists online : False
Views : 405