title: System.Data.Odbc support of SQL Server datetime2, time, date, datetimeoffset types
body: <p>According to <a href="https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements" rel="nofollow noreferrer">ODBC documentation</a>, ODBC supports the SQL 2008+ types like <code>datetime2</code>, <code>date</code>, <code>time</code> and <code>datetimeoffset</code>.</p>
  
  <p>But I am unable to use them with .Net <code>System.Data.Odbc</code> <a href="https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcconnection" rel="nofollow noreferrer">connections</a> and <a href="https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbccommand" rel="nofollow noreferrer">commands</a>, excepted <code>Date</code>. 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:</p>
  
  <ul>
  <li><p>Trying to read a <code>Time</code> column with a <code>System.Data.Odbc</code> reader <code>GetValue</code>
  yields:</p>
  
  <pre><code>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)
  </code></pre></li>
  <li><p>Trying to read a <code>Time</code> column with a <code>System.Data.Odbc</code> reader <code>GetTime</code> cuts fractional seconds.</p></li>
  <li>Trying to set a <code>Time</code> parameter on an <code>OdbcCommand</code> cuts its fractional seconds.</li>
  <li><p>Trying to set a <code>DateTime2</code> parameter on an <code>OdbcCommand</code> yields:</p>
  
  <pre><code>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)
  </code></pre></li>
  <li><p>Setting a <code>DateTimeOffset</code> parameter yields the same error with just the db-type name accordingly changed.</p></li>
  <li><p>Trying to read a <code>DateTimeOffset</code> column with a <code>System.Data.Odbc</code> reader <code>GetValue</code>
  yields:</p>
  
  <pre><code>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)
  </code></pre></li>
  <li><p>Trying to read a <code>DateTimeOffset</code> column with a <code>System.Data.Odbc</code> reader <code>GetDateTime</code> cuts offset but preserves fractional seconds (all 7 digits).</p></li>
  <li>Checking <a href="https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbctype" rel="nofollow noreferrer"><code>OdbcType</code></a> enumeration, it does not have types for <code>datetimeoffset</code> or <code>datetime2</code>.</li>
  <li><p>Trying to dodge the trouble for <code>datetime2</code> by using <code>DbType.DateTime</code> instead for parameter:</p>
  
  <ul>
  <li><p>Requires to set the parameter scale, otherwise fails with:</p>
  
  <pre><code>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.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
     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.ExecuteNonQuery()
  </code></pre></li>
  <li><p>When setting the parameter scale to 7, cuts fractional milliseconds even if the column in database is <code>datetime2</code>. This gives the one millisecond precision (compared to SQL Server <code>datetime</code> ten thirds of ms precision), but <code>datetime2</code> can go down to 100ns. Catching the query with SQL Server Profiler, we can see this:</p>
  
  <pre><code>exec sp_executesql N'select count(*) from Types2008 where [DateTime2] = @P1',
  N'@P1 datetime2','2017-10-26 20:01:55.1630000'
  </code></pre>
  
  <p>So the value is <code>datetime2</code> typed but has its fractional milliseconds zeroed out (yes my original value was not having them already zeroed).</p>
  
  <p>By the way this <code>datetime2</code> typing causes issues when the underlying table column is <code>datetime</code> instead, because it occurs in this case too. It causes <code>where</code> equality conditions to fail matching on two thirds of <code>datetime</code> values. Those ending with <code>3</code> or <code>7</code> milliseconds are not considered equal to the "same" value when typed <code>datetime2</code> with four trailing zero. Only those ending with 0 millisecond match.</p></li>
  </ul></li>
  </ul>
  
  <p>Is <code>System.Data.Odbc</code> just quite lagging behind ODBC support of SQL Server types? Or is there any settings/code to adjust for having it supporting those types?</p>
  
  <p>Those results were obtained with .Net Framework 4.7.1, a SQL Server Express 2016, and as connection string:</p>
  
  <pre><code>Driver={SQL Server Native Client 11.0};
  Server=.\sql_express;
  Database=test;
  Trusted_Connection=yes;
  Mars_Connection=yes;
  </code></pre>
  
  <p>Troubles reproduced too with .Net Core 2.0 using System.Data.Odbc 4.5.0-preview1-25829-02 from MyGet.</p>
  
  <p>Here is a complete <a href="https://gist.github.com/fredericDelaporte/e1563b1510ce31a01e7d9dc9e9387e6d" rel="nofollow noreferrer">gist</a>.</p>