New post
Question
· Jun 25

SQL DatePart Not Working With ISO 8601 Formatted Date & Time

#SQL #InterSystems IRIS for Health

Given a properly formatted ISO 8601 date time of 2024-06-23T06:03:00Z using SQL DatePart results in an error:

  [SQLCODE: <-400>:<Fatal error occurred>]

  [%msg: <Invalid input to DATEPART() function: datepart='ss', datetime value='2024-06-23T06:03:00Z')>]

If I remove the trailing Z (for Zulu / UTC time) and leave the T, DatePart works fine.

I have also tried various ± offsets from UTC e.g. +0400 and that also results in the same SQL error

I can Trim the trailing "Z", but I would hope that DatePart would work with an acceptably formatted ISO 8601 date time string without having to go through the machinations of trimming the data.

Any help or suggestions on how to use SQL DatePart with ISO 8601 formatted Date Time strings would be appreciated.

This is the query I was experimenting with:

select 'YEAR: '||DATEPART(YEAR,'2024-06-23T06:03:00Z') 
UNION
select 'MONTH: '||DATEPART(MONTH,'2024-06-23T06:03:00Z') 
UNION
select 'DAY: '||DATEPART(DAY,'2024-06-23T06:03:00Z')
UNION
select 'HOUR: '||DATEPART(HOUR,'2024-06-23T06:03:00Z')
UNION 
select 'MINUTE: '||DATEPART(MINUTE,'2024-06-23T06:03:00Z')
UNION
select 'SECOND: '||DATEPART(SECOND,'2024-06-23T06:03:00Z')

Product version: IRIS 2023.1
$ZV: IS for Windows (x86-64) 2023.1.2 (Build 450_0_22859) Mon Dec 4 2023 14:32:44 EST
Discussion (2)2
Log in or sign up to continue
Enrico Parisi · Jun 25

ISO 8601 date format is not an accepted format for the SQL DATEPART function.

You can check the supported formats in the DATAPART SQL Documentation.

If I remove the trailing Z (for Zulu / UTC time) and leave the T, DatePart works fine

I'm afraid it does not woks fine:

select 'YEAR: '||DATEPART(YEAR,'2024-06-23T06:03:00') 

result:

YEAR: 1900

Try:

select 'YEAR: '||DATEPART(YEAR,$TRANSLATE('2024-06-23T06:03:00Z','TZ',' '))

0 0
Vitaliy Serdtsev · Jun 26

Hi!

You need first convert the formatted date and time string to a standard timestamp. Unfortunately, the built-in TO_TIMESTAMP function is not suitable for your case.
Next, you need determine whether the time zone will be taken into account or not.

For tests, you can use https://www.timestamp-converter.com / (ISO 8601 section).

Here is a small example:

Class dc.test Extends %RegisteredObject
{

ClassMethod ISO2TS(
  strISO As %String,
  withTZ As %Boolean = 0) As %TimeStamp [ SqlProc ]
{
  s tsUTC=##class(%TimeStamp).XSDToLogical(strISO)
  q $s(withTZ:##class(%UTC).ConvertUTCtoLocal(tsUTC),1:tsUTC)
}

ClassMethod Test()
{
   f s="2024-06-23T06:03:00Z","2024-06-23T06:03:00-02:45" {
     s iso=s,
       utc=##class(%TimeStamp).XSDToLogical(iso),
       tz=##class(%UTC).ConvertUTCtoLocal(utc)

     w $$$FormatText("ISO 8601 = %2%1Date Time (UTC) = %3%1Date Time (your time zone) = %4%1",$$$NL,iso,utc,tz),!
   }
}

}

Result (for me):

USER>d ##class(dc.test).Test()
ISO 8601 = 2024-06-23T06:03:00Z
Date Time (UTC) = 2024-06-23 06:03:00
Date Time (your time zone) = 2024-06-23 09:03:00.000
 
ISO 8601 = 2024-06-23T06:03:00-02:45
Date Time (UTC) = 2024-06-23 08:48:00
Date Time (your time zone) = 2024-06-23 11:48:00.000

Using via SQL:

SELECT
  YEAR(ts) "YEAR",
  MONTH(ts) "MONTH",
  DAY(ts) "DAY",
  {fn HOUR(ts)} "HOUR",
  {fn MINUTE(ts)} "MINUTE",
  {fn SECOND(ts)} "SECOND"
FROM (SELECT dc.test_ISO2TS('2024-06-23T06:03:00Z',0) ts)
0 0