How to update of incorrect timestamp format in a DB2 table?


As per the standard DB2 definitions, timestamp holds 10 bytes in DB2 storage and 26 bytes in corresponding COBOL storage (PIC X(26)). It is in the format YYYY-MM-DDHH. MM.SS.NNNNNN. Where,

YYYY:- Year | MM:- Month | DD:- Date | HH:- Hour | MM:- Minutes | SS:- Seconds | NNNNNN:- Milliseconds

As per the scenario given in the question, the timestamp is “2020-07-01 23:14”. Clearly, the format of the timestamp is incorrect as the correct format is YYYY-MM-DDHH. MM.SS.NNNNNN. In this case if we will try to insert this incorrectly formatted timestamp in a DB2 table column which is defined as timestamp datatype, we will get - 180 DB2 error code. The IBM documentation defines -180 error code as−

The length or string representation of a DATE, TIME, or TIMESTAMP value does
not conform to any valid format.

Advertisements