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

You should use the TRY_CONVERT function Docs

It will return null if value is incorrect

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

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 

Tags:

Sql Server