Question
Kurro Lopez · Nov 4

How to convert datetime ISO 8601 to ObjectScript datetime

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
00
1 0 7 145
Log in or sign up to continue

$zdth()

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

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

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.

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.

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) {
  r=##class(%TimeStamp).XSDToLogical($e(ts,1,22)_":"_$e(ts,*-1,*))
  r," => "
  q $zdth(r,3)
 }

Output:

USER>do ^test
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

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

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