SQL生成XML时如何强制日期格式为DD/MM/YYYY？

我太懂你对接遗留系统的痛苦了——这套系统只认dd/mm/yyyy格式的日期，SQL生成的XML里只要是yyyy-mm-dd就会出问题，之前试了CONVERT 103和声明VARCHAR都没搞定对吧？

问题出在SQL Server对XML中日期类型的默认序列化行为：当你返回的是DATE/DATETIME类型数据时，不管你怎么转换，只要最终返回的字段类型还是日期，SQL Server就会自动把它转成ISO标准的yyyy-mm-dd格式塞进XML里。所以核心解决思路是：把日期明确转换为VARCHAR字符串类型，并且指定103格式（dd/mm/yyyy），确保返回给XML的是纯字符串，不是日期类型。

下面是修改后的完整代码，我把所有Date1到Date4的字段都做了调整：

SELECT TOP 1 
    1 'job/queue', 
    @Branch 'job/branch', 
    CASE WHEN myli.PolRef@ IS NOT NULL THEN 'update-broomsrisk' ELSE 'create-broomsrisk' END 'parameters/yzt/char20.1', 
    SUBSTRING(@PolicyRef,1,6) 'broomsdata/broomsclient/bcm/refno', 
    @PolicyRef 'broomsdata/broomspolicy/bpy/refno', 
    CASE WHEN myli.PolRef@ IS NOT NULL THEN myli.key@ ELSE NULL END 'broomsdata/broomspolicy/myli/KEY', 
    -- 修改Date1：转换为VARCHAR(10)的dd/mm/yyyy格式
    (SELECT CONVERT(VARCHAR(10), ISNULL(ct2.convictiondate, ct2.offencedate), 103) FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 1) 'broomsdata/broomspolicy/myli/Date1', 
    (SELECT ct2.code FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 1) 'broomsdata/broomspolicy/myli/Code1', 
    (SELECT ct2.fine FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 1) 'broomsdata/broomspolicy/myli/Fine1', 
    (SELECT ct2.noofpoints FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 1) 'broomsdata/broomspolicy/myli/Points1', 
    -- 修改Date2
    (SELECT CONVERT(VARCHAR(10), ISNULL(ct2.convictiondate, ct2.offencedate), 103) FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 2) 'broomsdata/broomspolicy/myli/Date2', 
    (SELECT ct2.code FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 2) 'broomsdata/broomspolicy/myli/Code2', 
    (SELECT ct2.fine FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 2) 'broomsdata/broomspolicy/myli/Fine2', 
    (SELECT ct2.noofpoints FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 2) 'broomsdata/broomspolicy/myli/Points2', 
    -- 修改Date3
    (SELECT CONVERT(VARCHAR(10), ISNULL(ct2.convictiondate, ct2.offencedate), 103) FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 3) 'broomsdata/broomspolicy/myli/Date3', 
    (SELECT ct2.code FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 3) 'broomsdata/broomspolicy/myli/Code3', 
    (SELECT ct2.fine FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 3) 'broomsdata/broomspolicy/myli/Fine3', 
    (SELECT ct2.noofpoints FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 3) 'broomsdata/broomspolicy/myli/Points3', 
    -- 修改Date4
    (SELECT CONVERT(VARCHAR(10), ISNULL(ct2.convictiondate, ct2.offencedate), 103) FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 4) 'broomsdata/broomspolicy/myli/Date4', 
    (SELECT ct2.code FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 4) 'broomsdata/broomspolicy/myli/Code4', 
    (SELECT ct2.fine FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 4) 'broomsdata/broomspolicy/myli/Fine4', 
    (SELECT ct2.noofpoints FROM #ConvictionTable AS ct2 WHERE ct2.RowNo = 4) 'broomsdata/broomspolicy/myli/Points4' 
FROM #ConvictionTable AS ct 
LEFT OUTER JOIN [FIG-SQL-SRV].InfoCentre.dbo.ic_BD_MYLI AS myli ON ct.branch = myli.B@ AND ct.policyRef = myli.PolRef@ 
FOR XML PATH('xmlexecute'), TYPE;

关键说明： #

1. CONVERT(VARCHAR(10), 日期字段, 103)：103是SQL Server里对应英国/欧洲格式（dd/mm/yyyy）的样式代码，指定VARCHAR(10)刚好容纳10位的日期字符串，避免多余空格。
2. 子查询内部转换：必须在子查询里就把日期转成字符串，这样返回给外层SELECT的就是VARCHAR类型，XML序列化时会直接使用这个字符串值，不会再自动转换为ISO格式。
3. 处理NULL值：保留了你原来的ISNULL逻辑，如果convictiondate为NULL就用offencedate，转换后如果还是NULL，XML里会显示空节点或者不包含该节点（取决于你的XML设置），如果需要显示空字符串，可以改成ISNULL(CONVERT(VARCHAR(10), ISNULL(ct2.convictiondate, ct2.offencedate), 103), '')。

这样修改后，生成的XML里Date1到Date4就会严格按照dd/mm/yyyy格式显示，应该能解决遗留系统的录入异常问题了。

内容的提问来源于stack exchange，提问作者Lynchie