TSQL拼接年日月字符串转换DATE类型报错求助

你的问题出在拼接字符串转DATE时，存在无效的日期格式或非法数据，导致转换失败。以下是具体排查和解决步骤：

第一步：定位无效数据 #

先运行以下查询，找出所有无法转换为DATE的拼接字符串，明确问题所在：

;WITH CTE AS (
SELECT t.AccountNumber, t.Birthday, t.DOBSource
    , MAX(CASE WHEN a01c.CodeType = 'ddcbyear' THEN a01c.CodeValue ELSE NULL END) AS bYR
    , MAX(CASE WHEN a01c.CodeType = 'ddcbmonth' THEN a01c.CodeValue ELSE NULL END) AS bMO
    , MAX(CASE WHEN a01c.CodeType = 'ddcbday' THEN CASE WHEN LEN(a01c.CodeValue) = 1 THEN CONCAT('0', a01c.CodeValue) ELSE a01c.CodeValue END END) AS bDY
FROM dbo.sA01cAccountCodes A01C (NOLOCK)
INNER JOIN BGTEMP.dbo.tmp22189_FirstPass t
ON a01c.AccountNumber = t.AccountNumber 
WHERE A01C.CodeType IN ('ddcbyear', 'ddcbmonth', 'ddcbday')
AND A01C.CodeValue NOT LIKE '%/%'
AND A01C.Active = 1
AND (t.Birthday IS NULL
    AND t.DOBSource IS NULL)
GROUP BY t.AccountNumber, t.Birthday, t.DOBSource
)
SELECT 
    AccountNumber,
    bMO + '/' + bDY + '/' + bYR AS 待转换日期字符串,
    bYR AS 年份,
    bMO AS 月份,
    bDY AS 日期
FROM CTE
WHERE bYR IS NOT NULL
  AND bMO IS NOT NULL
  AND bDY IS NOT NULL
  AND TRY_CAST(bMO + '/' + bDY + '/' + bYR AS DATE) IS NULL

运行后会列出所有转换失败的行，你可以检查这些行的年、月、日是否存在以下问题：

• 月份不在1-12范围内
• 日期超出对应月份的最大天数（比如4月31日、非闰年的2月29日）
• 年、月、日包含非数字字符
• 年份是两位且解析结果不符合业务预期

第二步：安全转换方案 #

推荐使用DATEFROMPARTS函数（SQL Server 2012及以上版本支持），直接基于年、月、日的数值生成DATE，避免字符串拼接的格式问题，同时可以提前验证数据有效性：

方案1：严格验证后转换 #

;WITH CTE AS (
SELECT t.AccountNumber, t.Birthday, t.DOBSource
    , MAX(CASE WHEN a01c.CodeType = 'ddcbyear' THEN a01c.CodeValue ELSE NULL END) AS bYR
    , MAX(CASE WHEN a01c.CodeType = 'ddcbmonth' THEN a01c.CodeValue ELSE NULL END) AS bMO
    , MAX(CASE WHEN a01c.CodeType = 'ddcbday' THEN CASE WHEN LEN(a01c.CodeValue) = 1 THEN CONCAT('0', a01c.CodeValue) ELSE a01c.CodeValue END END) AS bDY
FROM dbo.sA01cAccountCodes A01C (NOLOCK)
INNER JOIN BGTEMP.dbo.tmp22189_FirstPass t
ON a01c.AccountNumber = t.AccountNumber 
WHERE A01C.CodeType IN ('ddcbyear', 'ddcbmonth', 'ddcbday')
AND A01C.CodeValue NOT LIKE '%/%'
AND A01C.Active = 1
AND (t.Birthday IS NULL
    AND t.DOBSource IS NULL)
GROUP BY t.AccountNumber, t.Birthday, t.DOBSource
)
UPDATE t
SET t.Birthday = DATEFROMPARTS(CAST(c.bYR AS INT), CAST(c.bMO AS INT), CAST(c.bDY AS INT))
    , t.DOBSource = 'A01c'
FROM CTE c
INNER JOIN BGTEMP.dbo.tmp22189_FirstPass t
ON c.AccountNumber = t.AccountNumber
WHERE bYR IS NOT NULL
  AND bMO IS NOT NULL
  AND bDY IS NOT NULL
  -- 验证年、月、日是否为有效数字
  AND TRY_CAST(c.bYR AS INT) IS NOT NULL
  AND TRY_CAST(c.bMO AS INT) IS NOT NULL
  AND TRY_CAST(c.bDY AS INT) IS NOT NULL
  -- 验证月份范围
  AND CAST(c.bMO AS INT) BETWEEN 1 AND 12
  -- 基础日期范围校验
  AND CAST(c.bDY AS INT) BETWEEN 1 AND 31

方案2：容错式转换（跳过无效数据） #

如果不想因为个别无效数据导致整个更新失败，可以用TRY_CAST配合DATEFROMPARTS，自动跳过无法转换的行：

;WITH CTE AS (
SELECT t.AccountNumber, t.Birthday, t.DOBSource
    , MAX(CASE WHEN a01c.CodeType = 'ddcbyear' THEN a01c.CodeValue ELSE NULL END) AS bYR
    , MAX(CASE WHEN a01c.CodeType = 'ddcbmonth' THEN a01c.CodeValue ELSE NULL END) AS bMO
    , MAX(CASE WHEN a01c.CodeType = 'ddcbday' THEN CASE WHEN LEN(a01c.CodeValue) = 1 THEN CONCAT('0', a01c.CodeValue) ELSE a01c.CodeValue END END) AS bDY
FROM dbo.sA01cAccountCodes A01C (NOLOCK)
INNER JOIN BGTEMP.dbo.tmp22189_FirstPass t
ON a01c.AccountNumber = t.AccountNumber 
WHERE A01C.CodeType IN ('ddcbyear', 'ddcbmonth', 'ddcbday')
AND A01C.CodeValue NOT LIKE '%/%'
AND A01C.Active = 1
AND (t.Birthday IS NULL
    AND t.DOBSource IS NULL)
GROUP BY t.AccountNumber, t.Birthday, t.DOBSource
)
UPDATE t
SET t.Birthday = TRY_CAST(DATEFROMPARTS(TRY_CAST(c.bYR AS INT), TRY_CAST(c.bMO AS INT), TRY_CAST(c.bDY AS INT)) AS DATE)
    , t.DOBSource = 'A01c'
FROM CTE c
INNER JOIN BGTEMP.dbo.tmp22189_FirstPass t
ON c.AccountNumber = t.AccountNumber
WHERE bYR IS NOT NULL
  AND bMO IS NOT NULL
  AND bDY IS NOT NULL
  -- 只更新能成功转换的行
  AND TRY_CAST(DATEFROMPARTS(TRY_CAST(c.bYR AS INT), TRY_CAST(c.bMO AS INT), TRY_CAST(c.bDY AS INT)) AS DATE) IS NOT NULL

额外注意事项 #

• 如果你的SQL Server版本低于2012，无法使用DATEFROMPARTS，可以改用TRY_CONVERT并指定格式（格式101代表mm/dd/yyyy）：

  TRY_CONVERT(DATE, c.bMO + '/' + c.bDY + '/' + c.bYR, 101)
  

• 对于两位年份的情况，SQL Server默认会将00-49解析为2000-2049，50-99解析为1950-1999，如果业务逻辑需要调整，可以手动拼接四位年份（比如CASE WHEN LEN(c.bYR)=2 THEN '20' + c.bYR ELSE c.bYR END）。

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