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

Question

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?

Answer 1

You should use the TRY_CONVERT function Docs

It will return null if value is incorrect

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

Answer 2

If you're on SQL Server 2012 or later, and your database is in compatibility level 110 or higher, you can use TRY_CONVERT.

If not, substituting an inline table valued function for your scalar valued function should help.

CREATE FUNCTION dbo.scrub_datetime2
(
    @date datetime2
)
RETURNS TABLE
    WITH SCHEMABINDING 
AS
RETURN 
SELECT 
    CASE WHEN @date < '1753-01-01' 
         THEN NULL
         ELSE CONVERT(date, @date)
    END AS date_converted;
GO