Help creating a function

Question

Hello:

I am trying to create a function:

CREATE FUNCTION [dbo].[fStringToDate]
(@pString varchar(8))
RETURNS DATE
AS
BEGIN TRY

    DECLARE @Return DATE

    Set @pString = TRIM(@pString)
    IF LEN(@pString) = 8 
        BEGIN
            Set @Return = CAST(SUBSTRING(@pString,5,2)+'/'+LEFT(@pString,2)+'/'+LEFT(@pString,4) as DATE);
    END;
    IF LEN(@pString) = 6 
        BEGIN
            Set @Return = CAST(RIGHT(@pString,2)+'/01/'+LEFT(@pString,4) AS DATE);
        END;
    IF LEN(@pString) = 4
        BEGIN
            Set @Return = CAST('01/01/'+@pString AS DATE);
        END;
    RETURN @Return;

END TRY;  

BEGIN CATCH  

    SET @Return = NULL; 
    RETURN @Return;

END CATCH; 

But I get the following error messages:

Msg 102, Level 15, State 1, Procedure fStringToDate, Line 24 [Batch Start Line 0]
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Procedure fStringToDate, Line 31 [Batch Start Line 0]
Incorrect syntax near 'CATCH'.

What am I doing wrong?

Venki

Answer 1

If you want to catch the conversion problems, then consider TRY_CAST or TRY_CONVERT. In addition, fix some defects of initial approach:

CREATE FUNCTION [dbo].[fStringToDate]
    (@pString varchar(8))
RETURNS date
AS
BEGIN
    declare @return date

    declare @s as varchar(max) = TRIM(@pString)

    set @s =
        case LEN(@s)
        when 8 then 
            SUBSTRING(@s, 5, 2) + '/' + RIGHT(@s, 2) + '/' + LEFT(@s, 4)
        when 6 then
            RIGHT(@s, 2) + '/01/' + LEFT(@s, 4)
        when 4 then
            '01/01/' + @s
        end

    set @return = TRY_CAST(@s as date)

    return @return
END

Answer 2

You cannot use TRY/CATCH in a user defined function. That is documented in
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15
(scroll down to the bottom of the REMARKS section).

Errors in functions must be caught in the calling code. So put the call to your function in a TRY/CATCH block.

Also, the function must be enclosed in a BEGIN END block.

So your function should look like

CREATE FUNCTION [dbo].[fStringToDate]  
(@pString varchar(8))  
RETURNS DATE  
AS  
BEGIN  
  
         DECLARE @Return DATE  
         Set @pString = TRIM(@pString)  
         IF LEN(@pString) = 8   
             BEGIN  
                 Set @Return = CAST(SUBSTRING(@pString,5,2)+'/'+LEFT(@pString,2)+'/'+LEFT(@pString,4) as DATE);  
         END;  
         IF LEN(@pString) = 6   
             BEGIN  
                 Set @Return = CAST(RIGHT(@pString,2)+'/01/'+LEFT(@pString,4) AS DATE);  
             END;  
         IF LEN(@pString) = 4  
             BEGIN  
                 Set @Return = CAST('01/01/'+@pString AS DATE);  
             END;  
         RETURN @Return;  
          
          
         SET @Return = NULL;   
         RETURN @Return;  
          
END  

Tom