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