How can one bind datetime2(SQL_C_TYPE_TIMESTAMP) using SQLBindParameter?

I am trying to bind datetime2 parameter using SQLBindParameter for SQL_TYPE_TIMESTAMP data type as below

SQLBindParameter(hStmt, 7, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 0, 0, &datetime2, 0, NULL);

also tried this:

rc = SQLBindParameter(hStmt, 8, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_TIMESTAMP_LEN + 1, 7, &rec.datetime2, 0, NULL);

rc is 0

when I exceute the query (INSERT) SQLExecDirect(hStmt, const_cast<wchar_t*>(query.c_str()), SQL_NTS); I get 22008 sqlstate error indicating Datetime field overflow;

I've looked for any sample code on this data type and couldn't find any working example, is there a ninja out there who has a solution for this type? it is fine for SQL_TYPE_TIME with precision 7.

Solution

I get the same error in SQL Server 2014: If I bind using nResult = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP, 0, 0, &ts, sizeof(ts), &cbValue);, I get:

ERROR; native: 0; state: 22008; msg: [Microsoft][ODBC Driver 11 for SQLerver]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

So, I tried to examine what the server actually expects, with code like this:

nResult = SQLPrepare(hstmt, (SQLWCHAR*)L"INSERT INTO tTestTable (myTestCol, d2) VALUES(100, ?)", SQL_NTS);

SQLSMALLINT   DataType, DecimalDigits, Nullable;
SQLUINTEGER   ParamSize;

nResult = SQLDescribeParam(hstmt, 1, &DataType, &ParamSize, &DecimalDigits, &Nullable);
if (!SQL_SUCCEEDED(nResult))
{
    printErrStmt(hstmt);
}
std::wcout << L"ParamSize: " << ParamSize << L"; DecimalDigits: " << DecimalDigits << std::endl;

This prints out:

ParamSize: 27; DecimalDigits: 7

So, lets try with 27 and 7 and a fraction of 123 - and I still get the error:

ERROR; native: 0; state: 22008; msg: [Microsoft][ODBC Driver 11 for SQL erver]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

But then I found the sample here at microsoft: https://msdn.microsoft.com/de-de/library/ff878122%28v=sql.120%29.aspx This sample makes things even more confusing, as they do exactly the same thing? Wait - the difference is, they use a fraction value of only 100 - can that make a difference? Yes, it does. Changing the fraction to 100 makes things work. Why?

Lets look at SQL Server 2014 directly. If I inserted a Row (from ODBC) with a fraction of 100 this gets displayed (in SQL Server Management Studio) as: 1999-02-03 08:20:30.0000001. Remember what the fraction is exactly: From the docs at MS: https://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx

[b] The value of the fraction field is the number of billionths of a second and ranges from 0 through 999,999,999 (1 less than 1 billion). For example, the value of the fraction field for a half-second is 500,000,000, for a thousandth of a second (one millisecond) is 1,000,000, for a millionth of a second (one microsecond) is 1,000, and for a billionth of a second (one nanosecond) is 1.

So: A fraction of 100 would be 100 billionths of a second, this is 000,000,100. But the Datetime2 field has a precision of 7. As the last part is 00 there are no rounding errors. But if you pass in 123 this would be 000,000,123. This cannot be stored in a datetime with a precision of 7.. If we change the 123to 12300, the thing can be stored: It matches up to 000,012,300, this fits into the datetime with a precision of 7, and SQL Server finally displays: 1999-02-03 08:20:30.0000123.

I hope this helps, and I hope I understood and explained the fraction-thing right.