使用ODBC Driver 18向Azure SQL插入Timestamp报22008错误求助

执行向Azure SQL Server插入Timestamp值时触发错误：

[Microsoft][ODBC Driver 18 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding. SqlState = 22008

环境信息 #

• ODBC驱动：ODBC Driver 18 for SQL Server
• ODBC版本：3.0（SQL_OV_ODBC3）
• Azure SQL Server版本：Microsoft SQL Azure (RTM) - 12.0.2000.8
• 表字段：starttime类型为datetime2(2)（仅支持2位小数秒精度，即百分秒级别）

复现代码 #

std::string sQuery("insert into DB_TEST.backlog_period (periodid, starttime)  values (?, ?)");
SQLHSTMT hstmt = SQL_NULL_HSTMT;    // Statement handle
SQLRETURN retcode;          // Return status
WCHAR* wsQuery = string_to_whar(sQuery);
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hstmt);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
{
    std::cerr << "Error";
    return;
}
SQL_NUMERIC_STRUCT* pOid = new SQL_NUMERIC_STRUCT;
SQL_TIMESTAMP_STRUCT* m_pTimeStamp = new SQL_TIMESTAMP_STRUCT;
size_t nPos = 0;
SYSTEMTIME Time;
GetSystemTime(&Time);
ConvertDoubleToNumericStruct(9999, *pOid);

m_pTimeStamp.year = Time.wYear;
m_pTimeStamp.month = Time.wMonth;
m_pTimeStamp.day = Time.wDay;
m_pTimeStamp.hour = Time.wHour;
m_pTimeStamp.minute = Time.wMinute;
m_pTimeStamp.second = Time.wSecond;
m_pTimeStamp.fraction = Time.wMilliseconds;

SQLWCHAR ColumnName[MAX_COL_NAME_LEN];
SQLSMALLINT    ColumnNameLen;
SQLSMALLINT    ColumnDataType;
SQLULEN        ColumnDataSize;
SQLSMALLINT    ColumnDataDigits;
SQLSMALLINT    ColumnDataNullable;
retcode = SQLPrepare(hstmt, (SQLWCHAR*)wsQuery, SQL_NTSL);

SQLSMALLINT NumParams;
SQLNumParams(hstmt, &NumParams);

retcode = SQLDescribeParam(hstmt,  1, &ColumnDataType, &ColumnDataSize, &ColumnDataDigits, &ColumnDataNullable);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, ColumnDataSize, ColumnDataDigits, pOid, sizeof(SQL_NUMERIC_STRUCT), 0);
//above call is equivalent to     
//SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 10, 0, pOid, sizeof(SQL_NUMERIC_STRUCT), 0);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);

retcode = SQLDescribeParam(hstmt, 2, &ColumnDataType, &ColumnDataSize, &ColumnDataDigits, &ColumnDataNullable);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, ColumnDataSize, ColumnDataDigits, m_pTimeStamp, sizeof(SQL_TIMESTAMP_STRUCT), 0);
    //above call is equivalent to     
//SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 22, 2, m_pTimeStamp, sizeof(SQL_TIMESTAMP_STRUCT), 0);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);

retcode = SQLExecute(hstmt); // This is getting failed
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);  

问题原因 #

SQL_TIMESTAMP_STRUCT的fraction字段单位是十亿分之一秒，代码中直接将SYSTEMTIME.wMilliseconds（毫秒，即千分之一秒，数值范围0-999）赋值给它，相当于传递了3位小数的精度，但表字段datetime2(2)仅支持2位小数秒，导致精度溢出触发错误。

解决方案 #

方案1：调整fraction值适配字段精度 #

将毫秒转换为2位精度的十亿分之一秒，修改赋值代码：

// 将毫秒转为百分秒（保留2位精度），再转换为十亿分之一秒
m_pTimeStamp.fraction = (Time.wMilliseconds / 10) * 10000000;

或者通过算术运算截断第三位：

m_pTimeStamp.fraction = (Time.wMilliseconds * 1000000) / 10;

方案2：修改表字段精度 #

如果业务允许毫秒级精度，将starttime字段类型改为datetime2(3)，可直接兼容毫秒值，无需修改代码。

方案3：显式指定绑定参数的scale #

在SQLBindParameter调用时，显式指定scale为2，确保和表字段精度匹配：

retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 22, 2, m_pTimeStamp, sizeof(SQL_TIMESTAMP_STRUCT), 0);

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