日期铸件:插入时错误，但在选择时不会插入

Question

我编写了以下查询，将3个varchar列转换为1 dateTime列：

select 
    MYKEY, 
    CAST(
            CAST(YEAR AS VARCHAR(4)) +
            RIGHT('0' + CAST(MONTH AS VARCHAR(2)), 2) +
            RIGHT('0' + CAST(DAY AS VARCHAR(2)), 2) as DATETIME) as AD_BORN
    from SOURCE
    where YEAR IS NOT NULL AND MONTH IS NOT NULL AND DAY IS NOT NULL AND YEAR<>''

它有效，并为我提供了预期的日期列表。但是，当我尝试将其结果插入新表中时，我会遇到一个错误：

SQL Server转换在转换日期和/或时间时失败 字符串

这是我的说法：

select 
    MYKEY, 
    CAST(
            CAST(YEAR AS VARCHAR(4)) +
            RIGHT('0' + CAST(MONTH AS VARCHAR(2)), 2) +
            RIGHT('0' + CAST(DAY AS VARCHAR(2)), 2) as DATETIME) as AD_BORN
    into MY_DATES
    from SOURCE
    where YEAR IS NOT NULL AND MONTH IS NOT NULL AND DAY IS NOT NULL AND YEAR<>''

my_dates在此查询之前不存在。源中的蒙特和日是Varchar(分别为4、2和2(。它们可能包含零值。

这是怎么回事？

Answer 1

无需填充字符串。如果2012 使用try_convert()

示例

Declare @YourTable Table ([Year] varchar(50),[Month] varchar(50),[Day] varchar(50))
Insert Into @YourTable Values 
 (2018,1,25)
,(2018,5,55) -- Bogus Date
Select *
      ,AsDate = try_convert(date,[Month]+'/'+[Day]+'/'+[Year])
 From @YourTable

返回

Year    Month   Day AsDate
2018    1   25  2018-01-25
2018    5   55  NULL        -- Bogus Date