How do I fix this Case Statement to check current and future year without getting an error?

Question

I need to check a date field to determine if it is in the current fiscal year e.g. 2019, or in the next fiscal year, e.g. 2020, depending on if the month is between January and July. If the month is <= 7, the current year should be assigned, but if the month is > 7, then the next year is to be assigned.

Using a staging table, the SQL I tried is as follows:

SELECT * FROM StagingTable
WHERE StagingTable.FY = 
    CASE WHEN MONTH (GETDATE()) <= 7 
         THEN YEAR (GETDATE())
    ELSE DATEPART(YYYY,DATEADD(YY, 1, GETDATE()))

I also tried using a the following simple code to determine my error, but just couldn't figure out why I am getting the error.

SELECT CASE 
    WHEN MONTH (GETDATE()) <= 7 
    THEN YEAR (GETDATE())
    ELSE DATEPART(YYYY,DATEADD(YY, 1, GETDATE())) 

I am expecting to get either 2019 or 2020 assigned to the StagingTable.FY column. The code looks fairly simple, but I just can't get it to work. Any suggestions?

Answer 1

You are missing the END for CASE

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Or

CASE WHEN MONTH (GETDATE()) <= 7 
     THEN YEAR (GETDATE())
     ELSE YEAR (GETDATE()) + 1
END;