IT37132: SQL0180N IS RETURNED WHEN TRY TO INSERT ODBC FORMAT TIMESTAMP.

Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Our docs claims that ODBC format timestamp can be used as an
  input.
  Moreover, different flavours of ODBC timestamp that use "T"
  (yyyy-MM-ddThh:mm:ss)  or "-" (yyyy-MM-dd-hh:mm:ss) as delimiter
  to separate date and time are equivalent to ODBC format that use
  space/blank to separate date and time (yyyy-MM-dd hh:mm:ss)
  
  For example, '2018-03-22 08:30:58.7', '2018-03-22-08:30:58.7',
  and '2018-03-22T08:30:58.7' are all equivalent.
  
  more info:
  https://www.ibm.com/docs/en/db2/11.5?topic=list-datetime-values
  
  However, SQL0180N is returned when tried to insert timestamp
  using formats that use "T" and "-" as delimiter
  
  $ db2 "create table t1(i int, t timestamp)"
  $ db2 "insert into t1 values(1, '2018-03-22-08.30.58.7')"
  DB20000I  The SQL command completed successfully
  
  $ db2 "insert into t1 values(2, '2018-03-22 08:30:58.7')"
  DB20000I  The SQL command completed successfully.
  
  $ db2 "insert into t1 values(3, '2018-03-22-08:30:58.7')"
  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
  
  $ db2 "insert into t1 values(4, '2018-03-22T08:30:58.7')"
  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
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * all                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.1.4.7                                          *
  ****************************************************************
  

Problem conclusion

• Upgrade to 11.1.4.7
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP