Assigning 31.12.2099 to an OLE DB parameter of type DBTimeStamp doesnt work after changing to MSOLEDBSQL

246 Views Asked by At

To use the "OLE DB Driver for SQL Server" I changed the provider in the connection string from Provider=SQLOLEDB.1 to Provider=MSOLEDBSQL as it should be. Now I'm getting the error "Invalid date format" if I assign the date 31.12.2099 to an OLE DB parameter of the type DBTimeStamp:

cmd.Parameters.Add("@ValidFrom", OleDbType.DBTimeStamp).Value = new DateTime(2099, 12, 31);

I found out that OleDbType.DBTimeStamp (DBTYPE_DBTIMESTAMP) maps to the SQL SERVER data type smalldatetime, which has the range from 1900-01-01 through 2079-06-06. Indeed if I change it from 31.12.2099 to 06.06.2079, it works without a problem (see also https://learn.microsoft.com/en-us/answers/questions/175510/oledbtype-dbtimestamp-in-oledbdataadapter-error-be.html).

The solution would be to use OleDbType.DBDate instead of OleDbType.DBTimeStamp. But what if I want to keep the time too? So my questions are:

  1. Why didn't I have this problem before the change of the connection provider? It seems like this range limitation was ignored.
  2. Which type should I use now to keep both date and time? There are only three datetime related OLE DB types: DBDate,DBTime and DBTimeStamp.

Remarks

Trying to set the scale as described in this link didn't work (How to parameterize 12/30/1899 to SQL Server native client when DataTypeCompatility is on?).

var param = cmd.Parameters.Add("@ValidFrom", OleDbType.DBTimeStamp);
param.Scale = 3;
param.Value = new DateTime(2099, 12, 31);

I'm getting in this case the following error:

The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.

I got this same error, when I changed the connection string as mentioned before. Then I solved it by adding DataTypeCompatibility=80 to the connection string and it was fine (see also https://social.msdn.microsoft.com/Forums/en-US/1241147c-42a1-40a5-b4c6-374374e462cd/the-fractional-part-of-the-provided-time-value-overflows-the-scale-of-the-corresponding-sql-server?forum=transactsql).

0

There are 0 best solutions below