SET语句中STRING转DATETIME2报错，如何用CONVERT/FORMAT解决？

问题描述 #

运行指定SQL脚本时触发错误：Conversion failed when converting date and/or time from character string.，经排查错误出在[CREATION_DATE]=t.CREATEDATE语句——目标字段CREATION_DATE为DATETIME2类型，源字段t.CREATEDATE是字符串类型，需通过转换函数完成赋值。

原SQL脚本：

WITH DOCUMENT_ID AS
  (SELECT 
    DWDOCID, 
    VALUE, 
    ROW_NUMBER() OVER(partition by DWDOCID Order by DWDOCID) AS RowNum
  FROM [SUVA]
    CROSS APPLY STRING_SPLIT(DWNAME,'^')
  WHERE DWDOCID = '1721' AND DOCUMENT_TYPE IS NULL
    )
UPDATE SUVA
  SET
    [STUDENT_NUMBER]=t.STUDENTNUMBER,
    [STUDENT_ID]=t.STUDENTID,
    [LAST_NAME]=t.LASTNAME,
    [FIRST_NAME]=t.FIRSTNAME,
    [CREATION_DATE]=t.CREATEDATE, 
    [DOCUMENT_TYPE]=t.DOCTYPE
FROM SUAM s INNER JOIN
    (
        SELECT DWDOCID,
            [1] AS DRAWER,
            [2] AS DOCID,
            [3] AS STUDENTNUMBER,
            [4] AS STUDENTID,
            [5] AS LASTNAME,
            [6] AS FIRSTNAME,
            [7] AS FIELD5,
            [8] AS DOCTYPE,
            [9] AS CREATEDATE,
            [10] AS DOCUMENTYEAR
        FROM DOCUMENT_ID
            PIVOT
            (MAX(VALUE)
        FOR RowNum in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS PVT
    )t 
ON s.dwdocid=t.dwdocid

解决方案 #

核心方法：用CONVERT显式转换 #

可以通过CONVERT函数将字符串类型的t.CREATEDATE转换为DATETIME2类型，需根据字符串的实际日期格式选择对应样式代码（常见样式示例）：

• 23：对应yyyy-MM-dd格式
• 101：对应MM/dd/yyyy格式
• 103：对应dd/MM/yyyy格式

修改后的赋值语句示例（假设日期格式为yyyy-MM-dd）：

[CREATION_DATE] = CONVERT(DATETIME2, t.CREATEDATE, 23)

可选：用TRY_CONVERT避免无效日期报错 #

如果t.CREATEDATE中存在不符合格式的无效值，可使用TRY_CONVERT替代CONVERT，它会返回NULL而非直接抛出错误：

[CREATION_DATE] = TRY_CONVERT(DATETIME2, t.CREATEDATE, 23)

不推荐使用FORMAT函数 #

FORMAT函数主打将日期/数值格式化为字符串，反向转换效率远低于CONVERT，不适合当前场景。

修改后的完整SQL脚本 #

WITH DOCUMENT_ID AS
  (SELECT 
    DWDOCID, 
    VALUE, 
    ROW_NUMBER() OVER(partition by DWDOCID Order by DWDOCID) AS RowNum
  FROM [SUVA]
    CROSS APPLY STRING_SPLIT(DWNAME,'^')
  WHERE DWDOCID = '1721' AND DOCUMENT_TYPE IS NULL
    )
UPDATE SUVA
  SET
    [STUDENT_NUMBER]=t.STUDENTNUMBER,
    [STUDENT_ID]=t.STUDENTID,
    [LAST_NAME]=t.LASTNAME,
    [FIRST_NAME]=t.FIRSTNAME,
    [CREATION_DATE] = CONVERT(DATETIME2, t.CREATEDATE, 23), -- 此处修改为显式转换，样式代码按需调整
    [DOCUMENT_TYPE]=t.DOCTYPE
FROM SUAM s INNER JOIN
    (
        SELECT DWDOCID,
            [1] AS DRAWER,
            [2] AS DOCID,
            [3] AS STUDENTNUMBER,
            [4] AS STUDENTID,
            [5] AS LASTNAME,
            [6] AS FIRSTNAME,
            [7] AS FIELD5,
            [8] AS DOCTYPE,
            [9] AS CREATEDATE,
            [10] AS DOCUMENTYEAR
        FROM DOCUMENT_ID
            PIVOT
            (MAX(VALUE)
        FOR RowNum in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) AS PVT
    )t 
ON s.dwdocid=t.dwdocid

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