DATEPART IN SELECT CASE Not working

Question

The objective of the code below is to mutiple number of month by 2 when someone join the club after january 1st. When I run it, I get an error message.

My research on similar usage here, shows my syntax is right. What am I doing wrong in my case?

Error 102: Incorrect syntax near '<'.`

This is the complete code:

SELECT 
    ID, [HIREDate],
    CASE [HIREDate]
       WHEN 12 - (MONTH([HIREDate])) <> 0
          THEN 12 - (MONTH([HIREDate])) * 2
       ELSE 0
    END AS Days 
FROM 
    dbo.table 
WHERE
    column1 <> ''

score 1 · Accepted Answer · answered Aug 25 '17 at 09:22

You're mixing the two "styles" of a CASE expression - try this:

SELECT 
    ID, [HIREDate],
    CASE 
       WHEN 12 - (MONTH([HIREDate])) <> 0
          THEN 12 - (MONTH([HIREDate])) * 2
       ELSE 0
    END AS Days 
FROM 
    dbo.table 
WHERE
    column1 <> ''

If you define a column just after the CASE like in your code:

CASE [HireDate]

then the WHEN clauses can only contain a value - not an expression:

CASE MONTH([HireDate])
   WHEN 1 THEN ....
   WHEN 2 THEN .....

If you need to have an expression in the WHEN clause, then you cannot put a column right after CASE - the WHEN clause needs to have all the information

DATEPART IN SELECT CASE Not working

1 Answers1