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

   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

				

Back to the top

Workaround

• 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

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.

Back to the top

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