Datetime field overflow with IBM Data Server Client v9.7fp5

2
votes

Using EntityFramework v4.1 and IBM Data Server Client v9.7fp5, DB first generated code based on a pre-defined DB2 table which has DATE columns. The DB2 DATE columns are mapped to .NET DateTime data types during the code generation.

When attempting to INSERT a row, receive the following error

ERROR [22008] [IBM] CLI0114E Datetime field overflow. SQLSTATE=22008

which makes sense, since .NET does not have a DATE data type, just DATETIME and that attribute would have more data then the DB2 DATE column would expect.

The questions are

  1. why doesn't the .NET base code automatically convert using ToShortDateString() and provide DB2 what it is expecting?

  2. what approaches could be used to override the .NET base logic and convert the value within application code before .NET submits the SQL transaction to DB2?

Any assistance or feedback would be appreciated. Thanks!

1
.netdatetimeodbcdb2

1 Answers

5
votes

Read datetime Data Type Conversions (ODBC). It defines various rules in datatype conversions. One is listed below - SQLSTATE 22008.

If truncation of seconds or fractional seconds occurs when converting from C to SQL, a diagnostic record is generated with SQLSTATE 22008 and the message "Datetime field overflow".

The key point here is to make sure that there happens no truncation in seconds/fractional seconds

DATE Datatype

If the DB2 database column is DATE datatype, create your variable as listed below:

new DateTime(2012,3,4); //No time part

TIMESTAMP DataType

If the DB2 database column is TIMESTAMP datatype, remove fraction of milliseconds:

dateTime = new DateTime(dateTime.Ticks - (dateTime.Ticks % TimeSpan.TicksPerSecond),dateTime.Kind);

Reference:

  1. How to truncate milliseconds off of a .NET DateTime
  2. Why is SQL Server losing a millisecond?

DB2 INSERT for DATE and TIMESTAMP

If you inserting with direct SQL statement in DB2,

for TimeStamp use format like '2012-12-17-16.53.57.285754'

and

for DATE use format like CAST ('2012-12-10' AS DATE)