× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.