Jason Washo Jason Washo - 1 month ago 7
SQL Question

Microsoft SQL Server - XML DateTime Formatting For Zero Milliseconds

I am trying to generate XML using Microsoft SQL Server's FOR XML. Three decimal milliseconds are critical. Using DATETIME2(3) works well for all situations where milliseconds are greater than 0. Zero milliseconds are dropped in the XML string conversion. Using normal formatting techniques does not work. What is the best way to have zero milliseconds appear as .000? Desired result is below:

DECLARE @WithMilliseconds AS DATETIME2(3);
DECLARE @ZeroMilliseconds AS DATETIME2(3);

SET @WithMilliseconds = (SELECT '2016-11-02 12:34:56.789');
SET @ZeroMilliseconds = (SELECT '2016-11-02 00:00:00.000');

SELECT @WithMilliseconds AS [WithMilliseconds] INTO #WithMilliseconds
SELECT @ZeroMilliseconds AS [ZeroMilliseconds] INTO #ZeroMilliseconds

SELECT * FROM #WithMilliseconds FOR XML AUTO
SELECT * FROM #ZeroMilliseconds FOR XML AUTO

-- DOESNT WORK
SELECT CAST([ZeroMilliseconds] AS DATETIME2(3)) AS [ZeroMilliseconds] FROM #ZeroMilliseconds FOR XML AUTO

-- DOESNT WORK
SELECT CONVERT(DATETIME2(3), [ZeroMilliseconds]) AS [ZeroMilliseconds] FROM #ZeroMilliseconds FOR XML AUTO


Result required:

<_x0023_ZeroMilliseconds ZeroMilliseconds="2016-11-02T00:00:00.000"/>

Answer

The format within XML is ISO8601 normally.

Note: When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.

CAST and CONVERT, DATETIME formats, section ISO8601 (Code 126).

The big question is: Why?

If everything is going the correct way, you should never think of XML as some text with extra characters. XML should never be parsed with string methods. Reading XML properly will - for sure! - return the correct value (if the XML is valid). It should be of no importance whether there are .000 or not...

You could enforce this by sending a properly formatted string (instead of a datetime-typed value) into your XML, but this smells...

UPDATE

A properly formatted string could be achieved like this

SELECT FORMAT(@WithMilliseconds,'yyyy-MM-ddTHH:mm:ss.fff')
SELECT FORMAT(@ZeroMilliseconds,'yyyy-MM-ddTHH:mm:ss.fff')

(Format() needs SQL Server 2012+)