title: SQL Server datetime in format yyyy-mm-ddThh:mi:ss.mmmZ
body: <p>For an XML message I need SQL server 2008 R2 to select a set of dates in the following format:</p>
  
  <pre><code>yyyy-mm-ddThh:mi:ss.mmmZ
  </code></pre>
  
  <p>I searched Google and Stack Overflow a bit and came to the following solution:</p>
  
  <pre><code>SELECT STUFF(CONVERT(VARCHAR(50), CAST(GETDATE() AS DATETIMEOFFSET), 127),24,4,'')
  </code></pre>
  
  <p>This is working fine except for cases where the milliseconds of the date are "000". In such cases it selects NULL.</p>
  
  <p>Can you please help me to find a solution that also works for dates where the milliseconds are "000"?</p>
  
  <p>You can use the following piece of code to play around with:</p>
  
  <pre><code>declare @timestamps table (
   i int,
   timestamp datetime
   )
  
  insert into @timestamps (i, timestamp)
  values
  (1, '2017-09-13 01:00:00.003'),
  (2, '2017-09-13 02:00:00.333'),
  (3, '2017-09-13 03:00:00.000'),
  (4, '2017-09-13 04:00:00')
  
  select i, timestamp, STUFF(CONVERT(VARCHAR(50), CAST(timestamp AS DATETIMEOFFSET), 127),24,4,'') from @timestamps
  </code></pre>