Formatting datetime values returned in a SELECT..FOR XML statement

Posted by TelJanini on Stack Overflow See other posts from Stack Overflow or by TelJanini
Published on 2012-06-21T20:58:32Z Indexed on 2012/06/21 21:16 UTC
Read the original article Hit count: 413

Filed under:
|
|
|
|

Consider the following table:

Orders

OrderId             Date               CustomerId
 1000       2012-06-05 20:03:12.000         51
 1001       2012-06-16 12:02:31.170         48  
 1002       2012-06-18 19:45:16.000         33

When I extract the Order data using FOR XML:

SELECT 
    OrderId AS 'Order/@Order-Id', 
    Date AS 'Order/ShipDate', 
    CustomerId AS 'Order/Customer' 
FROM Orders  
WHERE OrderId = 1000
FOR XML PATH ('')  

I get the following result:

<Order Order-Id="1000">
  <ShipDate>2010-02-20T16:03:12</ShipDate>
  <Customer>51</Customer>
</Order>  

The problem is, the ShipDate value in the XML file needs to be in the format M/DD/YYYY H:mm:ss PM. How can I change the output of the ShipDate in the XML file to the desired format?

Any help would be greatly appreciated!

© Stack Overflow or respective owner

Related posts about sql

Related posts about Xml