Microsoft KB Archive/259691

= FIX: Error Message &quot;Invalid Time Format&quot; When You Insert Date/Time With Maximum Values =

Article ID: 259691

Article Last Modified on 10/12/2005

-

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

-



This article was previously published under Q259691



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.



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
 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.  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.
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 

void CreateTable(HSTMT);

main {

// ODBC handles RETCODE rc; HENV henv; HDBC hdbc; HSTMT hstmt;

// Variables for SQLConnect

char * dsn = &quot;Pubs&quot;; char * uid = &quot;sa&quot;; char * pwd = &quot;&quot;;

// Variables for SQLDiagRec char mstate[6] = &quot;\0&quot;; long native = 0; char mtext[300] = &quot;\0&quot;; 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] = &quot;\0&quot;; long m_timeLen = 16; long sqlnts = SQL_NTS;

// miscellaneous variables char * strSQL = &quot;Select * from MillisecTest&quot;; char * strDropTable = &quot;Drop table MillisecTest&quot;; 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(&quot;\nError when attempting to bind as SQL_C_TIMESTAMP:\n\n&quot;); printf(&quot;\tSQLSTATE\t%s\n\tNative Error\t%i\n\tMessage\t%s\n\n&quot;,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(&quot;\nInvalid hour when timestamp is bound as SQL_C_CHAR: %s\n\n&quot;, 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(&quot;\nDo the \&quot;Press any key\&quot; thing...&quot;); getchar; return(TRUE); };

//--- CreateTable

void CreateTable(HSTMT hstmt) {

RETCODE rc = 0; char SqlStatements[2][90] = {&quot;Drop table MillisecTest&quot;, &quot;Create table MillisecTest (ID integer constraint index1 PRIMARY KEY, TimeTest datetime)&quot;};

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

}                   </li></ol>

Additional query words: timestamp invalid datetime max

Keywords: kbbug kbfix kbqfe kbmdac210sp2fix kbcodesnippet kbdatabase kbhotfixserver KB259691

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.