Re: ISO-8601 & Birgitta's Solution

[Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>]

This it?

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

On Mon, Feb 3, 2020 at 2:19 PM Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

> Back in December there was a discussion on ISO-8601 date format
> creation. SQL seemed to be the best/easiest approach.
>
> I recall (I think) that Birgitta Hauser made a suggestion that I made a
> note to follow up on.  Unfortunately, it was a mental note and I can no
> longer find her post.
> I believe Rob Berendt also liked it.
>
> Can anyone come up with her solution, or am I imaging things?
>
> Thanks,
> Sam
>
> --
> 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