ODBC连接SQL Server：如何解决‘Invalid character value for cast specification’错误？

Hey there! As an ODBC newbie, it’s totally normal to hit this kind of type conversion snag. That error usually means the ODBC driver can’t properly translate your string into a timestamp value. Let’s break down the best fixes and why your current code isn’t working:

1. Use ODBC’s Native Timestamp Struct (Recommended) #

Instead of passing a string and relying on implicit conversion, bind directly using SQL_TIMESTAMP_STRUCT—this is the most reliable approach because it avoids any string format ambiguities between different database drivers.

Here’s how to adjust your code:

// Define and populate the timestamp struct
SQL_TIMESTAMP_STRUCT timestamp;
timestamp.year = 2017;
timestamp.month = 10;
timestamp.day = 17;
timestamp.hour = 0;
timestamp.minute = 8;
timestamp.second = 22;
timestamp.fraction = 0; // Set to 0 if you don't need fractional seconds

// Bind the struct instead of a string
SQLRETURN rc = SQLBindParameter(hdc, i, SQL_PARAM_INPUT, 
                                SQL_C_TIMESTAMP, SQL_TYPE_TIMESTAMP, 
                                0, 0, &timestamp, sizeof(SQL_TIMESTAMP_STRUCT), NULL);

This way, you’re passing the date/time components directly in the format ODBC expects, so there’s no room for conversion errors from string parsing.

2. Fix Your String Binding (If You Must Use a String) #

If you want to stick with a string input, there are two key issues in your original code:

• You didn’t specify the correct length of your input string
• The driver might be expecting a precise format match

Update your code like this:

const char* value = "2017-10-17 00:08:22";
SQLLEN value_len = strlen(value); // Get the actual length of the string

SQLRETURN rc = SQLBindParameter(hdc, i, SQL_PARAM_INPUT, 
                                SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 
                                19, 0, (SQLPOINTER)value, value_len, &value_len);

Key Notes: #

• The 8th parameter (value_len) tells ODBC how long your input string is—setting it to 0 in your original code meant the driver couldn’t read the full string.
• Ensure your string strictly follows the ODBC timestamp format: YYYY-MM-DD HH:MI:SS (no extra spaces, no alternative separators like slashes).

3. Debug with More Detailed Error Info #

To get to the root of the problem faster, use SQLGetDiagRec to retrieve specific error details from the ODBC driver. This will tell you exactly what’s causing the cast failure:

SQLCHAR sql_state[6];
SQLCHAR error_msg[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER native_error;
SQLSMALLINT msg_length;

if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
    SQLGetDiagRec(SQL_HANDLE_STMT, hdc, 1, sql_state, &native_error, error_msg, sizeof(error_msg), &msg_length);
    printf("ODBC Error: %s - %s\n", sql_state, error_msg);
}

Final Tip #

Always prefer the struct approach for date/time values in ODBC—it’s type-safe, avoids driver-specific format quirks, and eliminates most conversion errors.

内容的提问来源于stack exchange，提问作者Rick