Search code examples
ibm-clouddashdb

dashDB: how to create a TIMESTAMP from an ISO 8601 date?

I have a VARCHAR field storing an iso 8601 date as follows: 2015-01-13T23:17:00

I would like to convert the date into a TIMESTAMP.

It seems that I have to strip out the 'T' to use the TIMESTAMP_FORMAT function: 

SELECT
    T.F1,
    T.F2,
    TIMESTAMP_FORMAT(
        REPLACE(T."log_date", 'T', ' '), 'YYYY-MM-DD HH24:MI:SS'
    ) TS
FROM
   "MYSCHEMA"."MYTABLE" T

This feels a bit clumsy. What is the recommended way of parsing ISO 8601 dates in dashDB?

Solution

  • It seems that the approach I taken in dashDB although a bit kludgey but is similar to what others have recommended for DB2. See some answers for DB2, here.