title: Microsoft SQL Server - XML DateTime Formatting For Zero Milliseconds
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Result required: </p>
  
  <pre><code>&lt;_x0023_ZeroMilliseconds ZeroMilliseconds="2016-11-02T00:00:00.000"/&gt;
  </code></pre>