System.Data.Odbc support of SQL Server datetime2, time, date, datetimeoffset types

[fredericDelaporte]

According to ODBC documentation, ODBC supports the SQL 2008+ types like datetime2, date, time and datetimeoffset.

But it seems impossible to use them with System.Data.Odbc available on MyGet since #19102, excepted Date. They look unsupported by this ODBC client, although I have the "ODBC Driver 13 for SQL Server" and its "SQL Server Native Client 11.0" driver:

• Trying to read a Time column with a System.Data.Odbc reader GetValue
  yields:

   System.ArgumentException : Unknown SQL type - SS_TIME_EX.
      at System.Data.Odbc.TypeMap.FromSqlType(SQL_TYPE sqltype)
      at System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i)
      at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
  

• Trying to read a Time column with a System.Data.Odbc reader GetTime cuts fractional seconds.

• Trying to set a Time parameter on an OdbcCommand cuts its fractional seconds.

• Trying to set a DateTime2 parameter on an OdbcCommand yields:

   System.ArgumentException : No mapping exists from DbType DateTime2 to a known
   OdbcType.
      at System.Data.Odbc.TypeMap.FromDbType(DbType dbType)
      at System.Data.Odbc.OdbcParameter.set_DbType(DbType value)
  

• Setting a DateTimeOffset parameter yields the same error with just the db-type name accordingly changed.

• Trying to read a DateTimeOffset column with a System.Data.Odbc reader GetValue
  yields:

   System.ArgumentException : Unknown SQL type - -155.
      at System.Data.Odbc.TypeMap.FromSqlType(SQL_TYPE sqltype)
      at System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i)
      at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
  

• Trying to read a DateTimeOffset column with a System.Data.Odbc reader GetDateTime cuts offset (but preserves fractional seconds (all 7 digits)).

• Checking OdbcType enumeration, it does not have types for datetimeoffset or datetime2.

• Trying to dodge the trouble for datetime2 by using DbType.DateTime instead for parameter:

  • Requires to set the parameter scale, otherwise fails with:

       System.Data.Odbc.OdbcException : ERROR [22008]
       [Microsoft][SQL Server Native Client 11.0]Datetime field overflow.
       Fractional second precision exceeds the scale specified in the parameter binding.
          at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
          at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
          at System.Data.Odbc.OdbcCommand.ExecuteScalar()
    

  • When setting the parameter scale to 7, cuts fractional milliseconds even if the column in database is datetime2. This gives the one millisecond precision (compared to SQL Server datetime ten thirds of ms precision), but datetime2 can go down to 100ns. Catching the query with SQL Server Profiler, we can see this:

       exec sp_executesql N'select count(*) from Types2008 where [DateTime2] = @P1',
       N'@P1 datetime2','2017-10-26 20:01:55.1630000'
    

    So the value is datetime2 typed but has its fractional milliseconds zeroed out (yes my original value was not having them already zeroed).

    By the way this datetime2 typing causes issues when the underlying table column is datetime instead, because it occurs in this case too. It causes where equality conditions to fail matching on two thirds of datetime values. Those ending with 3 or 7 milliseconds are not considered equal to the "same" value when typed datetime2 with four trailing zero. Only those ending with 0 millisecond match.

Those results were obtained with .Net Core 2.0, System.Data.Odbc 4.5.0-preview1-25829-02, a SQL Server Express 2016, and as connection string:

Driver={SQL Server Native Client 11.0};
Server=.\sql_express;
Database=test;
Trusted_Connection=yes;
Mars_Connection=yes;

Here is a complete gist of tests reproducing those issues.

Those troubles exist with .Net Framework too, and are reported here.

[divega]

Moving out of area-System.Data because it is a provider specific features request. SqlClient it is for now until we have a better way to track ODBC issues.

cc @saurabh500

[agilmore30]

Hi

I am getting a similar issue with DateTimeOffset as the OP.

The exception is 'Unknown SQL type - -155.Unknown SQL type - -155.)'

If you look at this link:
https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements?view=sql-server-2017

you see that DateTimeOffset

maps like this:

DatetimeOFFSET | SQL_SS_TIMESTAMPOFFSET | -155 (SQLNCLI.h)

and to ODBC Types:

SQL_SS_TIMESTAMPOFFSET	SQL_SS_TIMESTAMPOFFSET_STRUCT	SQL_C_SS_TIMESTAMPOFFSETSQL_C_BINARY (ODBC 3.5 and earlier)	0x4001 (sqlncli.h)SQL_BINARY (-2)

This occurs with the following ODBC Drivers

ODBC Driver 13 for SQL Server - 2017.140.1000.169
ODBC Driver 17 for SQL Server - 2017.172.00.01
SQL Native Client 11.0 - 2011.110.6540

It seems that it's by design that these clients return -155 for DateTimeOffset

As the OP noted OdbcType enumeration does not have types for datetimeoffset or -155

Is it possible that ODBC just doesn't support a type like DateTimeOffset? The closest I found is SQL_TYPE_UTCDATETIME.

Could the class library receive the data type as Binary then convert to DateTimeOffset?

[voltcode]

Any idea when those sql types will be available through odbc? It's been a while, lack of support for otherwise working types can result in really weird bugs in data access code.

[jackfoxy]

I am seeing this same problem.

[ammadj]

Hi, Is there any update on this? or any ETA?

[Bysza]

Same here.

[popisms]

Same issue.