SQL Server: if "datetime2" < 1753, then "datetime" NULL?
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
精彩评论