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.
WORKAROUND
Use the timestamp value with a date part as well as time part when
inserting or doing the conversion.
For SQLBindParameter, change the fCType to SQL_C_TIMESTAMP and define
rgbValue to be TIMESTAMP_STRUCT, and set appropriate values in date and
time parts of the structure.
For SQLExecDirect, use escape clause for timestamp data instead of the
escape clause for time data.
//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);
Direct Execution
SQLExecDirect(hstmt, "insert into test values({ts '1990-01-01
10:10:10'}",SQL_NTS)
This will set the date and time values correctly.