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!

Answers


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.


Need Your Help

Replace host in Uri

c# .net uri

What is the nicest way of replacing the host-part of an Uri using .NET?

Understanding the necessity of wait() and notify()

java multithreading

I have tried to understand the necessity to implement threads by using wait() and notify() when accessing shared resources or relying on their state.