10

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 fixed 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).

9
+50

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
*/
  • 6
    But I would just use TRY_CONVERT in the query and discard the whole idea of using an inefficient scalar UDF for this... – Aaron Bertrand Apr 28 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. – Tony Hinkle Apr 30 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. – Solomon Rutzky Apr 30 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. – Solomon Rutzky May 1 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. – Tony Hinkle May 1 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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