SQL Server:如果“datetime2”

My intention is to insert data from source table into target table. Source table has datetime2 column and target table has datetime column. If datetime2 value does not fit (< year 1753) into datetime field, it will be converted to null. Here is an example

我的目的是将源表中的数据插入到目标表中。源表有datetime2列，目标表有datetime列。如果datetime2值不适合(小于1753)到datetime字段，那么它将被转换为null。这是一个例子

DROP TABLE dbo.test1
--source table
CREATE TABLE dbo.test1 (wday DATETIME2 NULL)
go

INSERT  INTO dbo.test1
        (wday
        )
        SELECT  '2008-02-01 00:00:00.000'
        UNION ALL
        SELECT  '2009-02-01 00:00:00.000'
        UNION ALL
        SELECT  '0001-02-01 00:00:00.000'

DROP TABLE dbo.test2
--target table
CREATE TABLE dbo.test2 (wday DATETIME NULL)
go

--insert only valid datetime dates, < 1753 will be converted to nulls
INSERT INTO dbo.test2
        (wday
        )
        SELECT  CASE WHEN DATEDIFF(YEAR, dbo.test1.wday, GETDATE()) < 111
                     THEN NULL
                     ELSE CAST(dbo.test1.wday AS DATETIME)
                END
        FROM    dbo.test1

The code does not work. Also using datediff here is not valid logic, how to implement this?

代码不起作用。这里使用datediff也是无效的逻辑，如何实现呢?

2 个解决方案

#1

why not just

为什么不直接

INSERT  dbo.test2
        (wday
        )
        SELECT  CASE WHEN dbo.test1.wday < '17530101'
                     THEN NULL
                     ELSE CAST(dbo.test1.wday AS DATETIME)
                END
        FROM    dbo.test1

#2

Another option:

另一个选择:

--insert only valid datetime dates, < 1753 will be converted to nulls
INSERT INTO dbo.test2
        (wday
        )
        SELECT  CASE WHEN DATEPART(YEAR, dbo.test1.wday) < 1753
                     THEN NULL
                     ELSE CAST(dbo.test1.wday AS DATETIME)
                END
        FROM    dbo.test1

#1