How to create a function that parses dates-CodePudding

Hi I have the following code to create a function. Basically it parses dates and tells me the round number based on the date.

CREATE OR ALTER FUNCTION VITAL_TARGET.roundfind(dates date)
 RETURNS varchar
 
AS

BEGIN


case 
    when dates between '2020-06-08' and '2020-11-14' then return 'Round 1'
    when dates between '2020-11-15' and '2021-02-17' then return 'Round 2'
    when dates between '2021-02-18' and '2021-04-28' then return 'Round 3'
    when dates between '2021-04-29' and '2021-07-16' then return 'Round 4'
    when dates between '2021-07-16' and '2021-10-03' then return 'Round 5'
    when dates between '2021-10-04' and '2021-11-30' then return 'Round 6'
    when dates between '2021-12-01' and '2022-02-01' then return 'Round 7'
    when dates between '2021-02-02' and '2022-03-28' then return 'Round 8'
    when dates >= '2022-03-29' then return 'Round 9'
end;

The following error is showing up:

Msg 102, Level 15, State 1, Procedure roundfind, Line 1 [Batch Start Line 0]
Incorrect syntax near 'dates'.
Msg 178, Level 15, State 1, Procedure roundfind, Line 18 [Batch Start Line 0]
A RETURN statement with a return value cannot be used in this context.

CodePudding user response：

There is a lot of with your function. Firstly you define your parameter as a column called dates, not a parameter (@dates). Next you define you want the FUNCTION to return a varchar(1), meaning it'll only ever return NULL or 'R'.

Then you have you CASE expression which has statements for its scalar values; return 'Round 1' isn't a scalar value, it's s statement. As it's name suggests, a CASE expression is an expression, and as such it returns a scalar value, not a statement or boolean result.

In your CASE expression you also try to reference a column dates again, which doesn't exist as it doesn't have any content (there is no FROM).

You are also then missing an END, either for your CASE expression or your BEGIN:

CREATE OR ALTER FUNCTION VITAL_TARGET.roundfind (@dates date)
RETURNS varchar(7)
AS
BEGIN
    RETURN CASE WHEN @dates BETWEEN '2020-06-08' AND '2020-11-14' THEN 'Round 1'
                WHEN @dates BETWEEN '2020-11-15' AND '2021-02-17' THEN 'Round 2'
                WHEN @dates BETWEEN '2021-02-18' AND '2021-04-28' THEN 'Round 3'
                WHEN @dates BETWEEN '2021-04-29' AND '2021-07-16' THEN 'Round 4'
                WHEN @dates BETWEEN '2021-07-16' AND '2021-10-03' THEN 'Round 5'
                WHEN @dates BETWEEN '2021-10-04' AND '2021-11-30' THEN 'Round 6'
                WHEN @dates BETWEEN '2021-12-01' AND '2022-02-01' THEN 'Round 7'
                WHEN @dates BETWEEN '2021-02-02' AND '2022-03-28' THEN 'Round 8'
                WHEN @dates >= '2022-03-29' THEN 'Round 9'
           END;
END;

You can, however, make your CASE expression much more succinct with the following:

CREATE OR ALTER FUNCTION VITAL_TARGET.roundfind (@dates date)
RETURNS varchar(7)
AS
BEGIN
    RETURN CASE WHEN @dates >= '2022-03-29' THEN 'Round 9'
                WHEN @dates >= '2021-02-02' THEN 'Round 8'
                WHEN @dates >= '2021-12-01' THEN 'Round 7'
                WHEN @dates >= '2021-10-04' THEN 'Round 6'
                WHEN @dates >= '2021-07-16' THEN 'Round 5'
                WHEN @dates >= '2021-04-29' THEN 'Round 4'
                WHEN @dates >= '2021-02-18' THEN 'Round 3'
                WHEN @dates >= '2020-11-15' THEN 'Round 2'
                WHEN @dates >= '2020-06-08' THEN 'Round 1'     
           END;
END;