Formatting datetime values returned in a SELECT..FOR XML statement
- by TelJanini
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!
