Skip to content

Instantly share code, notes, and snippets.

@BirgittaHauser
Created January 3, 2020 09:57
Show Gist options
  • Save BirgittaHauser/422a4e470e0b4b4cad0f746f583430e8 to your computer and use it in GitHub Desktop.
Save BirgittaHauser/422a4e470e0b4b4cad0f746f583430e8 to your computer and use it in GitHub Desktop.
Convert a (Db2 for i) Timestamp into a character representation of a timestamp in the ISO8601 Format: YYYY-MM-DDTHH:MM:SS+/HH:MM
-- Convert a (Db2 for i) Timestamp into a character representation of a timestamp in the ISO8601 Format:
-- YYYY-MM-DDTHH:MM:SS+/HH:MM
Create Or Replace Function YOURSCHEMA.TIMESTAMP_ISO8601
(ParTimestamp Timestamp(6) Default Current_Timestamp,
ParTimeZone Decimal(6, 0) Default Current_Timezone)
Returns Varchar(26)
Language SQL
Modifies Sql Data
Specific TS_ISO8601
Deterministic
Set Option Dbgview = *SOURCE
Begin
Declare Continue Handler For SQLEXCEPTION Return 'Invalid';
Return (Translate(VarChar_Format(ParTimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'T', ' ') concat
Case When ParTimeZone < 0 Then '-' else '+' End concat
VarChar_Format('00010101' concat Right(Digits(ParTimeZone), 6), 'HH24:MI'));
End;
Label On Specific Routine YOURSCHEMA.TS_ISO8601
Is 'Timestamp in ISO8601 (YYYY-MM-DDTHH:MM:SS+/-HH:MM)';
Comment On Parameter Specific Routine YOURSCHEMA.TS_ISO8601
(PARTIMEZONE Is 'Timezone (-)HHMMSS');
Commit;
-- Executing the Function
Values(Timestamp_ISO8601());
Values(Timestamp_ISO8601(Current_Timestamp + 3 Days - 10 Years, -20000));
@Delvekior
Copy link

Hello, thank you very much for sharing, this is really interesting.
I wonder how this use of ISO8601 Format could stand extensively including the "Z" character when the time zone is UTC (+/- 0)...
I tried different things myself but I couldn't find an easy way, did you have this need too?

In addition, it's too bad this format may not be used to calculate time and date with this kind of result, such as
Values(Timestamp_ISO8601(Current_Timestamp)) + 3 Days;

Once again, thank you for sharing this post!

@jvriezen
Copy link

Thanks, Birgitta --- I just used this but extended it with an FF6 to get the fractional seconds also.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment