BUG: Time Value Converted to Datetime Using Access ODBC Driver

SYMPTOMS

When a value of type time is converted and inserted into a column in a Microsoft Access data source, defined as of type datetime, the converted value is incorrectly interpreted by Microsoft Access.

For example, consider the following SQL statements, executed using ODBC calls:

   SQLAllocEnv(&henv);
   SQLAllocConnect (henv, &hdbc);
   SQLDriverConnect (hdbc, hwnd, "", 0,
     szConnStrOut,cbConnStrOutMax,&cbConnStrOut, SQL_DRIVER_PROMPT);
   SQLAllocStmt(hdbc, &hstmt);
				

You can use either prepared execution or direct execution.

Prepared Execution

   SQLPrepare(hstmt,"insert into test values(?)",SQL_NTS);

   //Bind the parameter, define rgbValue to be a TIME_STRUCT

   SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_TIME,
                SQL_TIMESTAMP,16,rgbValue,cbValueMax,
   pcbValue);

   //Set appropriate time values in the rgbValue

   //Execute the prepared statement
   SQLExecute(hstmt);
				

Direct Execution

   SQLExecDirect(hstmt,"insert into test values({t '10:10:10'})",SQL_NTS);

      SQLFreeStmt (hstmt, SQL_DROP);
      SQLDisconnect(hdbc);
      SQLFreeConnect(hdbc);
      SQLFreeEnv(henv);
				

When a select is executed on that table, the value returned is as expected, with date part set to 0: However, if the table is opened in Microsoft Access, the datetime field contains time value as 12 AM with a date value of 12/30/9999.

   //Bind the parameter, define rgbValue to be a TIMESTAMP_STRUCT

   SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,
   SQL_C_TIMESTAMP,SQL_TIMESTAMP,16,rgbValue,cbValueMax,pcbValue);
				

   SQLExecDirect(hstmt, "insert into test values({ts '1990-01-01
   10:10:10'}",SQL_NTS)