I need to get the current date as ISO 8601 without milliseconds.
Annotation 6 of learn microsoft states for style 127:
For a milliseconds (mmm) value of 0, the millisecond decimal fraction value won’t display. For example, the value 2022-11-07T18:26:20.000 displays as 2022-11-07T18:26:20.
I have following query:
select
getdate() as current_datetime,
datepart(millisecond, getdate()) as ms_of_current_dt,
dateadd(millisecond, -datepart(millisecond, getdate()), getdate()) as current_dt_without_ms,
convert(varchar, dateadd(millisecond, -datepart(millisecond, getdate()), getdate() at time zone 'Central European Standard Time'), 127) as current_dt_as_iso8601
It doesn’t always work:
| current_datetime | ms_of_current_dt | current_dt_without_ms | current_dt_as_iso8601 |
|---|---|---|---|
| 2024-09-25 17:01:21.697 | 696 | 2024-09-25 17:01:21.000 | 2024-09-25T15:01:21.001Z |
Sometimes I get .001 of milliseconds.
What can I do? I guess datepart() is too accurate. Is there a function that returns the current date and time as datetime2?
5
Just CONVERT the current SYSUTCDATETIME to a varchar of the appropriate length to the appropriate style; 19 and 127 respectively. Then you can concatenate the Z at the end:
SELECT CONVERT(varchar(19), SYSUTCDATETIME(),127)+'Z';
Alternatively, use a varchar(20) and cast SYSDATETIMEOFFSET to a datetimeoffset(0):
SELECT CONVERT(varchar(20), CONVERT(datetimeoffset(0), SYSDATETIMEOFFSET()),127);
db<>fiddle
With the information of SYSDATETIME() I was able to solve it with this query:
select convert(varchar(20), dateadd(nanosecond, -datepart(nanosecond, sysdatetime()), sysdatetime() at time zone 'Central European Standard Time'), 127) as current_dt_as_iso8601
Additional information: at time zone 'Central European Standard Time' is necessary otherwise one doesn’t get the Z at the end. Maybe the time zone has to be adjusted in other cases.
2