CONVERT datetime to ISO8601 drops milliseconds

_{Friday, December 16, 2011 5:50 PM | 1 vote}

I'm converting a datetime column to ISO8601 format with CONVERT(varchar(30), datetimeCol, 126). The documentation for CONVERT shows an output of yyyy-mm-ddThh:mi:ss.mmm, but I'm finding that any datetime with 000 as the millisecond value drops the milliseconds and is output as yyyy-mm-ddThh:mi:ss. The same problem exists with style 127.

Here is a simple, reproduceable example:

create table TimestampTest (ts datetime not null);
insert into TimestampTest VALUES (GETDATE());
insert into TimestampTest VALUES ('2011-12-16 10:45:00');
select CONVERT(varchar(30), ts, 126) AS ts_converted from TimestampTest;

Is there a way to enforce the milliseconds such that the ISO8601 timestamp is reliable?

_{Friday, December 16, 2011 6:14 PM ✅Answered}

Interesting and reproducible observation! It may be considered a bug, IMHO, if not documented clearly. You may create a connect issue or a thread in Documentation forum in case it's by design.For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Friday, December 16, 2011 6:48 PM ✅Answered}

Thanks, Naomi. I believe it is a bug; ISO8601 allows decimal fractions on any of the time segments but it should be consistent. I created Connect issue 714388.

_{Friday, December 16, 2011 5:58 PM}

Hello,

if I run the follwing statement I get miliseconds:

SELECT CONVERT(varchar(30), GetDate(), 126) as result

So for me it seems the data already don't have millisecond information.

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

_{Friday, December 16, 2011 6:02 PM}

Yes, I normally get milliseconds as in your statement. It is only when milliseconds = 000 that they are left off. The output from my above example is:

2011-12-16T10:45:50.700

2011-12-16T10:45:00

Whereas this is the output I expect:

2011-12-16T10:45:50.700

2011-12-16T10:45:00.000

_{Saturday, December 17, 2011 4:38 AM}

Thanks, Naomi. I believe it is a bug; ISO8601 allows decimal fractions on any of the time segments but it should be consistent. I created Connect issue 714388.

No, decimals are allowed only on the sends field of a DATETIME or TIME data type (note: field is the ANSI SQL term for the year, month, day, hour minute and second parts of a temporal data value; is is not part of a record. Bad choice of terms but we are stuck).

Instead of the Unix string CONVERT() museum function, have you tried CAST() with the new temporal data types? 

 CAST (foobar AS DATETIME2(n))    with n = 0 to 7? 

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

_{Saturday, December 17, 2011 12:13 PM | 1 vote}

Instead of the Unix string CONVERT() museum function, have you tried CAST() with the new temporal data types? 

 CAST (foobar AS DATETIME2(n))    with n = 0 to 7? 

Why didn't you try that before you posted? It will give you a string on the format YYYY-MM-DD hh:mm:ss.fff. That is, the T is missing.

Here is a workaround: convert to datetime2 and add a millisecond to the time to lure SQL Server:

create table TimestampTest (ts datetime2 not null);
insert into TimestampTest VALUES (GETDATE());
insert into TimestampTest VALUES ('2011-12-16 10:45:00');
select CONVERT(varchar(23), dateadd(ns, 1000, ts), 126),
             cast(ts AS varchar(30)) AS ts_converted
from TimestampTest;
go
DROP TABLE TimestampTest

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se