OledbType DBTimeStamp in OledbDataadapter error beyond 06/06/2079

Question

I develop a VB.NET application in Visual Studio 2013 accessing a SQL-Server database through an Oledb.OledbDataAdapter and I have a question about the way date/time columns are accessed.
To demonstrate the issue I created a database table Table_1 with different date/time columns as follows:

From the VS-Designer toolbox I then add an Oledb.OledbDataAdapter to my form and I configure the dataadapter on Table_1. This generates typical DELETE-, INSERT-, SELECT- and UPDATE-commands. The Parameter Collection for the UPDATECommand is as follows:

The data type for the Expiry_datetime column was generated as an OlebType.DBTimeStamp where I would expect an OledbType.DateTime. The same DBTimeStamp-type is also generated for the Expiry_datetime2 and Expiry_smalldatetime columns. Only the Expiry_date colums received an OledbType DBDate.

This all is in contrast with a SqlClient.SqlDataAdapter:

The SqlDataAdapter generates corresponding SqlTDbypes: DateTime for datetime-columns, DateTime2 for datetime2, Date for Date and SmallDateTime for smalldatetime.

Now back to the Oledb.OledbDatadapter.
The OledbType DBTimeStamp is working correctly in the INSERT- and UPDATE-command of the adapater, however only for dates smaller than June 6, 2079. When inserting or updating records with dates beyond June 6, 2079 the error 'Invalid date format' occurs. This date is known to be the max for a smalldatetime datatype , but as far as I know not for a TimeStamp.
So I have 2 questions:

1. Why is the Oledb-dataadapter configurator generating DBTimeStamps on datetime-colums?
2. Why is a dataadapter insert/update with DBTimeStamp erroneous for dates beyond June 6, 2079?

Answer 1

Hi @Henk Wisselink ,

Thank you so much for posting here.

1. Why is the Oledb-dataadapter configurator generating DBTimeStamps on datetime-colums?

There are only three datetime related OledbTypes: DBDate,DBTime and DBTimeStamp.

You could only use DBDate and DBTimeStamp which map to datetime.

Reference: OleDbType Enum

2. Why is a dataadapter insert/update with DBTimeStamp erroneous for dates beyond June 6, 2079?

There are three date types in SQL Server mapping to DBTYPE_DBTIMESTAMP : datetime,smalldatetime and datetime2.

But the date range of smalldatetime is from 1900-01-01 through 2079-06-06. So 2079-06-06 would be the maxinum value of DBTYPE_DBTIMESTAMP.

Reference:SQL Server Native Client Data Type Support for OLE DB Date and Time Improvements

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table