function to receive a character input and return date format (with incorrect input)

Question

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect inputs like this "2012ABCD". In that case, I want the function to return a fix date such as 2020-01-01. What I've written so far is:

Create Function ReturnDate
(@date varchar(8))

Returns date

  as

    begin
       declare @result date

          set @result = (select convert(date , @date,111))
                if(@@ROWCOUNT>0) return @result
                 else return '2020-01-01'
       return @result
    end

This doesn't work and I just don't know how to handle the second part (when the input is incorrect).

Answer 1

On SQL Server 2012 and later you can use TRY_CONVERT to check to see if the input can be converted. If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.

begin
   declare @result date
   set @result = COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01')
   return @result
end

You could also use a TRY CATCH block and return the fixed date in the CATCH block, but it's best practice to use TRY_CONVERT so that SQL Server doesn't have to handle an error as that requires more time and resources.

A function for this type of code will incur more overhead than simply using the same logic in the query, so if it is being called many times every second you might chew up significant resource by using a function for it. I understand that this may be called from numerous pieces of code so there is a desire to make it a function in case the default date needs to be changed--then it's no compiled code changes and just update this function.

If this code is going to be run a lot, you should consider other options that will provide better performance than a user-defined function. Please see Solomon's answer for an overview of your options and further explanation of why you might choose one over the other.

For example, the following shows the same logic implemented as an inline table-valued function, which needs to be used with CROSS APPLY if not supplied with a static value, but performs much better than a scalar UDF:

USE [tempdb];

GO
CREATE
OR ALTER -- comment out if using pre-SQL Server 2016 SP1
FUNCTION dbo.ReturnDate (@Date VARCHAR(8))
RETURNS TABLE
AS RETURN
  SELECT ISNULL(TRY_CONVERT(DATE, @Date, 111), '2020-01-01') AS [TheDate];
GO


SELECT *
FROM   (VALUES (1, '20120101'), (2, '2012ABCD')) tab(ID, Input)
CROSS APPLY dbo.ReturnDate(tab.[Input]) dt
/*
ID    Input       TheDate
1     20120101    2012-01-01
2     2012ABCD    2020-01-01
*/