New post
Question
· 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
Log in or sign up to continue
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 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>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
1 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