Re: Converting ISO dates to Timestamp format? -- MIDRANGE-L

On 16-Mar-2018 07:12 -0600, Bradley Stone wrote:

Are there any SQL functions that can take an ISO data like:

2018-03-07T23:07:56Z

And convert this into a timestamp when inserting into a PF?

Ignoring the proper effect of having also converted the character string value as ISO-Date-Column with UTC (e.g. named iso_Zdate_col) values into the local time from Zulu/UTC, the following untested expressions might suffice?:

A presumably less complex/compute-intense version [removing two recursed REPLACE scalar reference] of what Rob gave [forming a String Representation of Timestamp, as "IBM® SQL: 'yyyy-mm-dd-hh.mm.ss'" with fractional seconds truncated]:

left( iso_Zdate_col, 10 ) concat '-' concat
replace( substr(iso_Zdate_col, 12, 8), ':', '.' )

As alluded by Darren [forming a String Representation of Date, as "*ISO: 'yyyy-mm-dd'" for the first argument of the TIMESTAMP scalar, and forming a String Representation of Time, as "*JIS/*HMS: 'hh:mm:ss'" for the second argument of the TIMESTAMP scalar]:

timestamp( left( iso_Zdate_col, 10)
, substr(iso_Zdate_col, 12, 8) )

Given already by Birgitta [forming a String Representation of Timestamp, as "ISO timestamp: 'yyyy-mm-dd hh:mm:ss'" with fractional seconds truncated]:

translate( iso_Zdate_col, ' ', 'TZ')

And possibly, also, using a TO_DATE scalar name alternative; possibly the most conspicuous and simplest for what is being requested for the transformation of the DateTime-string-data, if even functional:

to_timestamp( iso_Zdate_col, 'MM/DD/YYYY HH:MI:SS ')

The latter of those however, was, in my experience, a crap-shoot. That is because, despite the claim in the docs that the "Separator characters specified in a /string-expression/ are used to separate components •and• are •not• required to match the separator characters specified in the /format-string/" for ">>-TIMESTAMP_FORMAT--(--string-expression--,--format-string…" (https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzscatsformat.htm), my actual experience differs; as I recall, the system-supplied UDF (as pseudo-built-in) fails to generate a TIMESTAMP value with that format-string, for DateTime values in the ISO 8601 format. ¿Perhaps, unstated by the docs, is that all of the extraneous [though meaningful, more than separator] characters in the /string-expression/ •also• must be one of the recognized "separator characters", of which neither of "T" or "Z" is?:

FWiW:

[Is there a name for date format used by DB2?](https://archive.midrange.com/midrange-l/201510/msg00466.html)

[SQL convert text mm/dd/yy to date and timestamp](https://archive.midrange.com/midrange-l/201607/msg00168.html)

I seem to recall one of the XML functions having the ability to recognize the noted ISO format 'YYYY-MM-DDThh:mm:ssZ' in an XML element, due to that format being a valid XSD DateTime data type with UTC [per lack of an actual timezone offset, wherein "Z" appears instead].