Db2

 View Only

 Unable to parse TIMESTAMP in c#

  • IBMChampion
Andrew G's profile image
Andrew G posted Mon May 05, 2025 12:20 PM

I'm facing a strange issue where I'm unable to parse a TIMESTAMP column from DB2. This problem only occurs when the time part is set to exactly midnight, as in the value "2024-11-26-00.00.00.000000."

To read data from the database, I'm using the package Net.IBM.Data.Db2 (version 6.0.0.300). My query looks like this:

string mySelectQuery = "SELECT * FROM XYZ";
DB2Command myCommand = new DB2Command(mySelectQuery, conn);
DB2DataReader reader = myCommand.ExecuteReader();
object value = reader.GetValue(0);

However, I'm encountering an exception:

System.InvalidCastException: Specified cast is not valid.  
at IBM.Data.DB2Types.DB2TimeStamp.get_Value()  
at IBM.Data.DB2Types.DB2TimeStamp.op_Explicit(DB2TimeStamp source)  
at IBM.Data.Db2.DB2DataWrapper.GetValue()

I then tried to retrieve the value as a DB2TimeStamp directly:

DB2TimeStamp value = reader.GetDB2TimeStamp(0);

Unfortunately, when I try to access the DateTime value using .Value, I get the same exception. Furthermore, when I cast the DB2TimeStamp value to a string, I end up with an incorrect value: "2024-11-25-24.00.00.000000."

Has anyone else experienced this issue, or does anyone have insights on how to work around it? Thanks!

Jan Nelken's profile image
Jan Nelken IBM Champion posted Mon May 05, 2025 09:42 PM

First of all - you are getting - or using - incorrect string representation of timestamp:

"2024-11-25-24.00.00.000000."
-------------------------------------^

This decimal point (dot) is wrong here:

db2inst1@ThinkPad-T410:~$ db2 "insert into t values ('2024-11-25-24.00.00.000000.')"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0180N  The syntax of the string representation of a datetime value is 
incorrect.  SQLSTATE=22007

I used Db2 CLP for your data:

db2inst1@ThinkPad-T410:~$ db2 describe table t

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
C                               SYSIBM    TIMESTAMP                   10     6 Yes   

  1 record(s) selected.

db2inst1@ThinkPad-T410:~$ db2 "insert into t values ('2024-11-26-00.00.00.000000')"
DB20000I  The SQL command completed successfully.
db2inst1@ThinkPad-T410:~$ db2 "insert into t values ('2024-11-25-24.00.00.000000')"
DB20000I  The SQL command completed successfully.
db2inst1@ThinkPad-T410:~$ db2 "select * from t"

C                         
--------------------------
2024-11-26-00.00.00.000000
2024-11-25-24.00.00.000000

  2 record(s) selected.

db2inst1@ThinkPad-T410:~$ 

Both values are correct; there is a note wrt to using 24 as hour:

A time is a three-part value (hour, minute, and second) designating a time of day under a 24-hour clock:

  • The range of the hour part is 0 - 24.
  • The range of the minute and second parts is 0 - 59.

If the hour is 24, the minute and second specifications are 0.

Important: Using the value 24 when representing hours in a time value might result in errors or unexpected data. To avoid this issue, use 00 instead of 24.

Related Content