I did not follow the complete thread, but here is a function which converts
any timestamp in any timezone into a character representation of a timestamp
in the following Format: YYYY-MM-DDTHH:MI:SS+/-HH:MI
If an error occurs, INVALID is returned.
(instead of DATE/TIME and SUBSTR, I used the VARCHAR_FORMAT Function)
Create Or Replace Function HSCOMMON10.TIMESTAMP_ISO8601
(ParTimestamp Timestamp(6) Default Current_Timestamp,
ParTimeZone Decimal(6, 0) Default Current_Timezone)
Returns Varchar(26)
Language SQL
Modifies Sql Data
Deterministic
Set Option Datfmt = *ISO, Dbgview = *SOURCE, Timfmt = *ISO
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;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Donnerstag, 2. Januar 2020 19:55
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Timestamp in ISO-8601 compliant format
Why does this ignore the time separator on the set option?
CREATE OR REPLACE FUNCTION ROB.TS8601 (
TIMESTAMPIN TIMESTAMP )
RETURNS CHAR(25)
LANGUAGE SQL
SPECIFIC ROB.TS8601
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
TIMSEP=':',
SRTSEQ = *HEX
BEGIN
DECLARE CHARTIMESTAMP CHAR ( 19 ) ;
DECLARE TIMESTAMPOUT CHAR ( 25 ) ;
SET CHARTIMESTAMP = LEFT ( CHAR ( TIMESTAMPIN ) , 19 ) ;
SET TIMESTAMPOUT = LEFT ( CHARTIMESTAMP , 10 )
CONCAT 'T' CONCAT SUBSTR ( CHARTIMESTAMP , 12 , 8 )
CONCAT ( CASE WHEN CURRENT TIMEZONE < 0 THEN '-' ELSE '+' END )
CONCAT SUBSTR ( DIGITS ( CURRENT TIMEZONE ) , 1 , 2 ) CONCAT ':' CONCAT
SUBSTR ( DIGITS ( CURRENT TIMEZONE ) , 3 , 2 )
;
RETURN TIMESTAMPOUT ;
END ;
Rob Berendt
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
