Written by

Kurro Lopez

Project Manager & Head of Interoperability at Salutic Soluciones, S.L.

MOD

Question Kurro Lopez · Nov 4, 2021

How to convert datetime ISO 8601 to ObjectScript datetime

#Caché

Hi all,

I have a process that recive a datetime in ISO 8601 format (YYYY-MM-DDThh:mm:ss+001) to %timestamp value.

Example: set myDatetime = "2021-11-04T11:10:00+0100"

I've triyed use $SYSTEM.SQL.CONVERT(myDatetime,"SQL_TIMESTAMP") but it doesn't work.

Any idea?

Regards,
Kurro Lopez

Product version: HealthShare 2017.2

$ZV: Cache for Windows (x86-64) 2017.2.1 (Build 801_3U) Thu Apr 12 2018 10:02:23 EDT

Discussion (7)1

Add reply

Comments

Vitaliy Serdtsev · Nov 4, 2021

$zdth()

USER>w $zdth("2021-11-04T11:10:00+0100",3,5)
66052,40200

0 0

Julius Kavay Nov 4, 2021 to Vitaliy Serdtsev

According to documentation, the tformat paramer 5 is ignored:

"Specify time in the form "hh:mm:ss+/-hh:mm" (24-hour clock). The time is specified as local time. The following optional suffix may be supplied, but is ignored: a plus (+) or minus (–) suffix followed by the offset of local time from Coordinated Universal Time (UTC). A minus sign (-hh:mm) indicates that the local time is earlier (westward) of the Greenwich meridian by the returned offset number of hours and minutes. A plus sign (+hh:mm) indicates that the local time is later (eastward) of the Greenwich meridian by the returned offset number of hours and minutes."

The same goes for the parameter values 6, 7 and 8

write $zdth("2021-11-04T11:10:00+0100",3,5)  --> 66052,40200
write $zdth("2021-11-04T11:10:00+0200",3,5)  --> 66052,40200
write $zdth("2021-11-04T11:10:00-0100",3,5)  --> 66052,40200

0 0

Vitaliy Serdtsev Nov 4, 2021 to Julius Kavay

I know, that's why I gave a link to the documentation so that the author would do the rest of the work himself, since ISO 8601 has many forms.

Here is another variant of the ISO8601ToDateTime method.

0 0

Julius Kavay Nov 4, 2021 to Vitaliy Serdtsev

Oh, one have to hover over the function name to see, that's a link! Maybe I have to blame my monitor because of the more grayish then bluish color.

0 0

Vitaliy Serdtsev Nov 19, 2021 to Julius Kavay

It's okay, I already have a minus. It looks like this is my last comment here.

UPD:

  w $$Iso8601ToTimeStamp("2021-10-22T08:00:00+0900"),!
  w $$Iso8601ToTimeStamp("2021-11-04T11:10:00+0100"),!
  w $$Iso8601ToTimeStamp("2021-11-04T11:10:00+0200"),!
  w $$Iso8601ToTimeStamp("2021-11-04T11:10:00-0140"),!

Iso8601ToTimeStamp(ts) {
  s r=##class(%TimeStamp).XSDToLogical($e(ts,1,22)_":"_$e(ts,*-1,*))
  w r," => "
  q $zdth(r,3)
 }

Output:

USER><FONT COLOR="#0000ff">do </FONT><FONT COLOR="#000000">^test</FONT>
2021-10-21 23:00:00 => 66038,82800
2021-11-04 10:10:00 => 66052,36600
2021-11-04 09:10:00 => 66052,33000
2021-11-04 12:50:00 => 66052,46200

0 0

Kurro Lopez Nov 4, 2021 to Julius Kavay

Thanks.. the 3,5 was the answer... now it's working !!!

0 0

Julius Kavay · Nov 4, 2021

Usually, I solve such problems (it's faster then searching for some funy SQL or other functions) by writing my own function/method/expression, depending on the current requirement.

ClassMethod TimeZoneToHorolog(tz)
{
   set t=$zdth(tz,3,5), t=t*86400+$p(t,",",2)+($e(tz,20,22)*60+$e(tz,23,24)*60)
   quit t\86400_","_(t#86400)
}

Assuming, tz contains a timezone formatted string like:

2021-11-04T11:10:00+0300

0 0