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


       declare @result date

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

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


1 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.

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

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];

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

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
  • 6
    But I would just use TRY_CONVERT in the query and discard the whole idea of using an inefficient scalar UDF for this... Commented Apr 28, 2019 at 13:16
  • 2
    I don't really care about the rep points, so I'm not saying it due to loss of such, but the community doesn't benefit from downvoting an obviously correct answer. Instead of downvoting, write an answer, edit mine, or leave a comment on what needs to be changed. OP asked for a function, and I realize that may not be the best solution, but it is the solution that was requested. Commented Apr 30, 2019 at 13:48
  • 3
    Tony: I did not down-vote, but I certainly agree that someone shouldn't down-vote without either providing the reasoning in a comment, or up-voting an existing comment that includes their reasoning. That said: 1) there is no FINALLY block in T-SQL (I think you meant CATCH). 2) you should probably mention that TRY_CONVERT started in 2012 (some folks are stuck pre-SQL Server 2012). 3) have you considered an Inline TVF? Those don't have the same performance issues as Scalar UDFs. Commented Apr 30, 2019 at 15:49
  • 1
    @TonyHinkle Thanks for making those edits, and for the reference to my S.O. answer :). Still, I'm not sure how many readers will be able to make the leap from seeing the UDF logic and reading that an inline TVF will be better, to successfully implementing the iTVF. So, I went ahead and added it to the end of your answer. Commented May 1, 2019 at 14:34
  • 1
    @SolomonRutzky Thanks. I am not really a SQL developer so that is still above my head. Maybe I shouldn't answer something like this, but it is a tremendous learning opportunity. Commented May 1, 2019 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.