I've got an etl project where the source has 60 datetime2 columns, and the target is entirely datetime.
A few days ago, we woke up to an empty data warehouse because one user at one hospital, for one patient, entered a surgery date of '1220-01-01'.
I temporarily got around the issue by couching each of the columns in a custom function:
ALTER function [dbo].[scrub_datetime2](@date datetime2) returns date as begin declare @return date set @return = case when @date <= '1800-01-01' then null else @date end return @return end
And this works ok for getting rid of the red text, but It also doubled the time the job takes to run. I'm happy to do my part, keeping the hamsters in the server room warm through christmas, but I can't really justify losing 2.5 hours of precious overnight time to this issue.
Question How would most efficiently check that a datetime2 value will fit in datetime?