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));
@edubois1976
Copy link

edubois1976 commented Dec 19, 2024

Thanks, Birgitta, I used it and extended with a FF3 to get milliseconds on the generated output

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