sql-server - 格式化 SELECT..FOR XML 语句中返回的日期时间值

Question

考虑下表:

订单

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

当我使用 FOR XML 提取订单数据时:

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

我得到以下结果:

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

问题是，XML 文件中的 ShipDate 值需要采用 M/DD/YYYY H:mm:ss PM 格式。如何将 XML 文件中 ShipDate 的输出更改为所需格式？

如有任何帮助，我们将不胜感激!

Answer 1

类似于 Andomar 的解决方案，但它提供了当时请求的 h:mm:ss PM 格式:

DECLARE @o TABLE(OrderId INT, [Date] DATETIME, CustomerId INT);

INSERT @o SELECT 1000,'2012-06-05 20:03:12',51
UNION ALL SELECT 1001,'2012-06-16 12:02:31',48  
UNION ALL SELECT 1002,'2012-06-18 19:45:16',33;

SELECT 
    OrderId AS 'Order/@Order-Id', 
    CONVERT(CHAR(10), [Date], 101) 
    + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), [Date], 22), 11))
    AS 'Order/ShipDate', 
    CustomerId AS 'Order/Customer' 
FROM @o --WHERE OrderId = 1000
FOR XML PATH ('');

结果:

<Order Order-Id="1000">
  <ShipDate>06/05/2012 8:03:12 PM</ShipDate>
  <Customer>51</Customer>
</Order>
<Order Order-Id="1001">
  <ShipDate>06/16/2012 12:02:31 PM</ShipDate>
  <Customer>48</Customer>
</Order>
<Order Order-Id="1002">
  <ShipDate>06/18/2012 7:45:16 PM</ShipDate>
  <Customer>33</Customer>
</Order>