DB2 Cast DateTime string with "T" separator
Help me please with the next problem. I have date time string in the next format(ISO 8601): 1999-12-31T23:59:59 and I need to cast it to TIMESTAMP value. The main problem in 'T' separator character. I have tried next query:
SELECT TIMESTAMP_FORMAT('1999-12-31T23:59:59','YYYY-MM-DD HH24:MI:SS') FROM ROMAN.EMPLOYEE;
and use different format strings, such as, YYYY-MM-DDTHH24:MI:SS, YYYY-MM-DD"T"HH24:MI:SS.
Could you provide me correct way to cast this type of strings without any character replacement and substrings. Thanks in advance!
There is no built-in function that can format a timestamp in ISO-8601 format in DB2 for Linux/UNIX/Windows.
As you have probably surmised, you can do this with REPLACE:
select TIMESTAMP_FORMAT(REPLACE('1999-12-31T23:59:59','T',' '), 'YYYY-MM-DD HH24:MI:SS') from ROMAN.EMPLOYEE;
It's trivial to create a user defined function (UDF) to handle this formatting for you as well so you don't have to out this long string in every query.
It may also be possible to do it via XQuery with and xs:dateTime, although this would be even more code than just embedding REPLACE in the call to TIMESTAMP_FORMAT.