Share via

OledbType DBTimeStamp in OledbDataadapter error beyond 06/06/2079

Henk Wisselink 1 Reputation point
2020-11-25T16:22:18.747+00:00

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:

42548-image.png

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:

42715-image.png

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:

42699-image.png

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?
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,191 Reputation points
    2020-11-26T03:07:59.703+00:00

    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.

    42883-oledbtype.png

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

    Reference: OleDbType Enum

    1. 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.

    42809-datetime.png

    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

    0 comments