SQL: UDF to convert string to date or return '0001-01-01'

sql-convert-string-to-date.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <[email protected]>
-- Create date: 2020.12.21
-- Description:	Convert string to DATE if NULL or invalid, else set to '0001-01-01'
-- =============================================
CREATE FUNCTION [dbo].[udf_ConvertStringToDate]
(
	@Field VARCHAR(500)
)
RETURNS DATE
AS
BEGIN
	DECLARE @StrField VARCHAR(500) = CAST(@Field AS VARCHAR(500));

	IF ISDATE(@StrField) = 0
		BEGIN
			SET @StrField = '0001-01-01';
		END

	RETURN CONVERT(DATE, @StrField);
END
GO