C# Datetime to ODBC Datetime conversion error

Question

We are trying to use ODBC with SQL Server 2012 for our new application because MS is phasing out OleDb and we want to try to make it easy (easier?) to port to other databases.

The problem is, when I try to use ODBC data access classes I get the following error when trying to save a datetime:

ERROR [22008] [Microsoft][SQL Server Native Client 11.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

okay, I get that sort of. C# has a higher level of precision than the ODBC parameter wants to use. I understand that the ODBC implementation doesn't like fractions of seconds, but what is the work around to preserve the datetime precision?

Thanks,

Dinsdale

EDIT: Okay here is the original parameter constructor:

String Name = "created";
DateTime DateTimeValue = DateTime.Now;
OdbcCommand cmd = new OdbcCommand();
cmd.CommandType = request.CommandType;
cmd.CommandText = request.Command;
OdbcParameter param;
param = new OdbcParameter(Name, OdbcType.DateTime);
param.Value = DateTimeValue;
cmd.Parameters.Add(param);

This code generates the following error: ERROR [22008] [Microsoft][SQL Server Native Client 11.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

The fix is to use an extended constructor like so:

param = new OdbcParameter(Name, OdbcType.DateTime, 23, System.Data.ParameterDirection.Input, false, 0, 3, Name, DataRowVersion.Current, DateTimeValue);

Thanks again to Kumod! Dinsdale

Answer 1

Fractional second precision exceeds the scale specified in the parameter binding.

It means that second precision is greater than Scale property of the OdbcParameter object. For DateTime formats:

yyyy-mm-dd hh:mm - Precision = 16, Scale = 0

yyyy-mm-dd hh:mm:ss - Precision = 19, Scale = 0

And here are those which need Scale property to be exceeded (it's 0 by default)

yyyy-mm-dd hh:mm:ss.f - Precision = 21, Scale = 1

yyyy-mm-dd hh:mm:ss.ff - Precision = 22, Scale = 2

yyyy-mm-dd hh:mm:ss.fff - Precision = 23, Scale = 3

each f is additional fractional second precision

OdbcParameter param = new OdbcParameter( "name", OdbcType.DateTime );
param.Value = DateTime.Now;
param.Precision = 23;
param.Scale = 3; // <-- you need to set this property