处理日期类型零值：decimal(17,6)字段转datetime报错如何解决

解决方案 #

问题原因 #

你原有语句报错的核心原因是遇到默认值0.000000时，转换得到的字符串'0'不属于合法的日期格式，触发转换失败。同时原有写法只取了整数部分，会直接丢失小数点后存储的时分秒数据。

兼容老版本数据库的通用写法 #

无需修改表结构，通过CASE判断提前过滤零值即可解决报错，同时保留完整的时分秒精度：

SELECT 
    CASE
        -- 零值可根据业务需要返回NULL或者指定默认日期，比如'1900-01-01'
        WHEN lastmoddatetime = 0 THEN NULL
        ELSE CONVERT(DATETIME,
            -- 把原始字符串20210820.171900处理为20210820 17:19:00格式
            STUFF(STUFF(STUFF(
                CONVERT(VARCHAR(20), lastmoddatetime, 2),
            9,1,' '),12,0,':'),15,0,':'),
        112)
    END AS lastmoddatetime_converted
FROM Table1

如果不需要保留时分秒，可直接改造原有语句 #

如果你只需要年月日维度的日期，直接加零值判断即可：

SELECT 
    CASE
        WHEN lastmoddatetime = 0 THEN NULL
        ELSE CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(INT, lastmoddatetime)), 0)
    END AS lastmoddatetime_converted
FROM Table1

后续使用DATEADD函数的示例 #

转换后的字段可以直接套入DATEADD逻辑，比如给转换后的日期加3天：

SELECT 
    DATEADD(DAY, 3,
        CASE
            WHEN lastmoddatetime = 0 THEN NULL
            ELSE CONVERT(DATETIME,
                STUFF(STUFF(STUFF(CONVERT(VARCHAR(20), lastmoddatetime, 2),9,1,' '),12,0,':'),15,0,':'),
            112)
        END
    ) AS calculated_date
FROM Table1

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