单条DATEADD查询正常，嵌入大查询后出现日期转换错误求助

这个报错的核心问题几乎可以肯定是UNION ALL两侧的结果集列类型不兼容，或者是隐式转换时格式不匹配导致的。咱们一步步拆解：

问题根源分析 #

你单独执行SELECT dateadd(d,-13,Period_DateEnd) Period FROM ReportingPeriod_tbl没问题，说明Period_DateEnd本身是合法的日期/时间类型，DATEADD的计算也正常。但嵌入到包含UNION ALL的查询后出错，问题出在UNION的两个子查询上：

1. 第一个子查询：SELECT format(WeekEnd,'d-MMM-yy') PeriodEnding FROM WeekEnd_tbl ...
FORMAT函数返回的是NVARCHAR类型的字符串（比如"5-Oct-23"这种格式化后的文本）。

2. 第二个子查询：你原本单独执行的是DATEADD返回日期类型，但嵌入时如果直接把DATEADD(d,-13,Period_DateEnd)和第一个子查询的字符串列做UNION，SQL Server会尝试隐式转换：把第一个子查询的字符串转换成日期类型，而你的字符串格式（d-MMM-yy）可能和当前SQL会话的日期格式设置不兼容；或者你在第二个子查询里用了FORMAT(Period_DateEnd,'MMM-yy'...)（比如返回"Oct-23"），这种缺少“日”部分的字符串无法被转换为完整日期，最终触发转换失败。

针对性解决方案 #

根据你的需求，分两种情况处理：

情况1：你需要返回格式化后的字符串 #

确保两个子查询都返回相同格式的字符串，统一用FORMAT处理：

SELECT PeriodEnding FROM (
    SELECT FORMAT(WeekEnd, 'd-MMM-yy') PeriodEnding 
    FROM WeekEnd_tbl 
    WHERE WeekEnd <= (SELECT TOP 1 WeekEnd FROM WeekEnd_tbl A WHERE A.IsCurrentWeek = 1)
    UNION ALL
    -- 把DATEADD的结果也用相同格式格式化
    SELECT FORMAT(DATEADD(d, -13, Period_DateEnd), 'd-MMM-yy') PeriodEnding 
    FROM ReportingPeriod_tbl
) AS CombinedResults

这样两个子查询都输出同格式的字符串，UNION ALL不会触发隐式转换，自然不会报错。

情况2：你需要返回原始日期类型 #

不要在子查询里用FORMAT，统一返回日期类型，格式化放到最外层：

SELECT FORMAT(PeriodEnding, 'd-MMM-yy') AS PeriodEnding FROM (
    SELECT WeekEnd PeriodEnding 
    FROM WeekEnd_tbl 
    WHERE WeekEnd <= (SELECT TOP 1 WeekEnd FROM WeekEnd_tbl A WHERE A.IsCurrentWeek = 1)
    UNION ALL
    SELECT DATEADD(d, -13, Period_DateEnd) PeriodEnding 
    FROM ReportingPeriod_tbl
) AS CombinedResults

这种方式更规范，因为日期类型的计算和存储更高效，格式化只做一次即可。

额外排查点 #

• 检查WeekEnd_tbl.WeekEnd和ReportingPeriod_tbl.Period_DateEnd的列类型，确保它们是DATE/DATETIME等日期类型，而不是VARCHAR（如果是字符串类型，要确保所有数据的日期格式一致）。
• 可以执行SELECT @@LANGUAGE查看当前会话的语言，比如如果是中文，FORMAT返回的月份是中文（如“10月”），这种字符串无法被隐式转换为日期，需要指定FORMAT的文化参数，比如FORMAT(WeekEnd, 'd-MMM-yy', 'en-US')来强制用英文格式。

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