CODEWITHSUNDEEP
db2
Stian
Stian

Reputation: 31

Converting string to timestamp in DB2 with to many fractions of a second

I am trying to convert a string on the on the following format to a timestamp in DB2: 2015-09-07T09:15:25.4788396+04:00

Problem is, DB2 only seems to handle 6 digit fractional seconds, not 7 as in my case. Any thoughts on a good workaround?

Upvotes: 3

Views: 1980

Answers (2)

Stavr00
Stavr00

Reputation: 3314

The format you have there matches the xs:dateTime pattern from XML. You can use this to your advantage and use implied XML parsing:

SELECT XMLCAST(XMLCAST('2015-09-07T09:15:25.4788396+04:00' AS XML) AS TIMESTAMP)
FROM SYSIBM.SYSDUMMY1

            1
--------------------------
2015-09-07-05:15:25.478839

Note the timestamp returned is in UTC, add + CURRENT TIMEZONE to return it as a local timestamp. Tested on DB2 z/OS DSN10015.

Upvotes: 2

smoore4
smoore4

Reputation: 4866

You will need to parse the timezone from the string. Once you have that you can get the UTC time from subtracting from "current timezone" then add the parsed timezone to it as shown below. You will also need to use REPLACE to change T to a dash and colons to dots. 

SELECT (TIMESTAMP('2015-09-07-09.15.25.4788396') - (current timezone)) + 4 hours FROM sysibm.sysdummy1;

Upvotes: 0

Related Questions