使用子串转换nvarchar至datetime出现异常结果的原因咨询

你的问题出在日期字符串的解析格式不匹配，以及拼接后的字符串被SQL Server错误解析了。

先拆解你的拼接逻辑：
对于StartDate=20191220，你拼接后的日期部分是SUBSTRING(StartDate,7,2) + '.' + SUBSTRING(StartDate,5,2) + '.' + SUBSTRING(StartDate,1,4)，也就是20.12.2019。
而TRY_CAST在解析这个字符串时，会依赖SQL Server的默认日期格式设置——如果你的服务器默认是mdy（月-日-年）格式，它会把20当成月份，但月份范围是1-12，20显然无效，所以TRY_CAST返回NULL。

再看案例1，StartDate=20191201拼接后是01.12.2019，SQL Server把01当成月份，12当成日，所以得到2019-01-12——这其实也是错误的结果，你想要的应该是2019-12-01，对吧？这说明你的拼接逻辑和SQL Server的解析逻辑完全不匹配。

方案1：用TRY_CONVERT指定明确的格式代码 #

既然你拼接的是dd.mm.yyyy hh:mm:ss格式，直接用TRY_CONVERT并指定格式代码104（德国日期格式，对应dd.mm.yyyy），这样不管服务器默认格式是什么，都能正确解析：

TRY_CONVERT(datetime, 
    SUBSTRING(xy.StartDate,7,2) + '.' + SUBSTRING(xy.StartDate,5,2) + '.' + SUBSTRING(xy.StartDate,1,4) 
    + ' ' + SUBSTRING(xy.StartTime,1,2) + ':' + SUBSTRING(xy.StartTime,3,2) + ':' + SUBSTRING(xy.StartTime,5,2),
    104
) as FULLSTARTDATE

这个方法会把20.12.2019 00:00:00正确转换成2019-12-20 00:00:00.000。

方案2：避免字符串拼接，直接用日期函数构建（更推荐） #

字符串拼接容易出错，不如直接用SQL Server的日期函数来构建datetime，完全规避格式解析问题：

TRY_CAST(
    DATEFROMPARTS(
        CAST(SUBSTRING(xy.StartDate, 1, 4) AS INT), -- 年
        CAST(SUBSTRING(xy.StartDate, 5, 2) AS INT), -- 月
        CAST(SUBSTRING(xy.StartDate, 7, 2) AS INT)  -- 日
    ) 
    + CAST(
        STUFF(STUFF(xy.StartTime, 3, 0, ':'), 6, 0, ':') AS TIME
    ) AS datetime
) as FULLSTARTDATE

• DATEFROMPARTS直接用年、月、日的数值构建日期，不会出错；
• STUFF(STUFF(StartTime,3,0,':'),6,0,':')把000000转换成00:00:00，再转成TIME类型，和日期相加得到完整的datetime。

• 案例1：StartDate=20191201，StartTime=000000，会得到2019-12-01 00:00:00.000（这才是你想要的正确结果）；
• 案例2：StartDate=20191220，StartTime=000000，会得到2019-12-20 00:00:00.000，不再返回NULL。

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