开发者

SQL Server: if "datetime2" < 1753, then "datetime" NULL?

开发者 https://www.devze.com 2023-02-24 19:40 出处:网络
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 dateti

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

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?


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


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
0

精彩评论

暂无评论...
验证码 换一张
取 消