Q:

How to bind a value to a stored proc parameter of type DATETIME2(7) using ODBC

What is the correct way to bind a value to a stored proc parameter of type DATETIME2(7) using ODBC. I want to do something like:
{CALL myproc (?,?)}

So, let's assume that the first argument is an int and the second one is a DATETIME2. I can do:
// Bind parameter 1, which is an INT
int val=5;
SQLLEN len=sizeof(val);
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 4, 0, 
                 &val, sizeof(val), &len);

// Bind parameter 2, a DATETIME2(7) value
// How do I do this??? How do I store the DATETIME2(7) value so that it can be
// bound and is not a string or degenerate DATETIME type

Thanks for any help!

A:

Let me start by saying that the ODBC datetime2 interaction was horribly documented in MSDN. After an inordinate amount of effort via trial and error, searching on MSDN (all the way up to SQL Server Denali CTP docs), myriads of forums, etc..., here is the way to bind a DATETIME2(7) parameter properly in ODBC:
// This is a sample SQL_TIMESTAMP_STRUCT object, representing:
// 1987-06-05T12:34:45.1234567
// Notice the two zeroes at the end of the fractional part?
// These _must_ be zeroes, because the fractional part is in nanoseconds!
// If you put non-zeroes in this part, you _will_ get a binding error.
SQL_TIMESTAMP_STRUCT ts=
  {1987,6,5,12,34,45,123456700};

SQLRETURN result=::SQLBindParameter(hStmt, 
  2, // Parameter idx in my original question
  SQL_PARAM_INPUT,
  SQL_C_TIMESTAMP,
  SQL_TYPE_TIMESTAMP,
  // Next is the length of the _string_ repr. of the DATETIME2(7) type! WTF???
  // It should be 20 + {the value of the precision of the DATETIME2}
  // Remember that we are using: 1987-06-05T12:34:45.1234567 for this example
  27, 
  7, // This is the precision of the DATETIME2
  &ts,
  sizeof(ts),
  NULL);
if (result!=SQL_SUCCESS)
  ...

I hope that this will be of help to the next person to suffer the wrath of ODBC and DATETIME2.

