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')