What's the most efficient way to safely convert from Datetime2 back to Datetime

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?

You should use the TRY_CONVERT function Docs

It will return null if value is incorrect

SELECT try_convert(datetime,'1700-01-01')

StackExchange Database Administrators Q#282069, answer score: 8