SQL WHEN function - How to fix Syntax Error?

Question

I am new to SQL coding and I am encountering only one error code so far. At the second line WHEN CONVERT:

(rd.254 is my table date column)

CASE WHEN CONVERT(date,rd.F254) Between 
CONVERT(date,getdate()-7) AND CONVERT(date, getdate()-1) THEN rd.F64 END AS QtyLastWeek

WHEN CONVERT(date,rd.F254) <= CONVERT(date,getdate()-8) THEN rd.F64 END AS Qty2WeeksAgo

I am trying to find and fix the syntax.

Answer 1

you have missing comma and case

CASE WHEN CONVERT(date,rd.F254) Between CONVERT(date,getdate()-7) AND CONVERT(date, getdate()-1) THEN 
   rd.F64 
END AS QtyLastWeek,
CASE WHEN CONVERT(date,rd.F254) <= CONVERT(date,getdate()-8) THEN 
   rd.F64 
END AS Qty2WeeksAgo

Answer 2

There is incorrect syntax. If you want to select 2 columns QtyLastWeek and Qty2WeeksAgo you have to use the comma after QtyLastWeek and use another CASE expression.

SELECT 
    CASE WHEN CONVERT(date,rd.F254) BETWEEN CONVERT(date,getdate()-7) AND CONVERT(date, getdate()-1) THEN rd.F64 END AS QtyLastWeek,
    CASE WHEN CONVERT(date,rd.F254) <= CONVERT(date,getdate()-8) THEN rd.F64 END AS Qty2WeeksAgo

Answer 3

You are missing case keyword in the second case statement. Try this !

CASE WHEN CONVERT(date,rd.F254) Between CONVERT(date,getdate()-7) AND CONVERT(date,getdate()-1) 
THEN rd.F64 
END AS QtyLastWeek,
CASE   WHEN CONVERT(date,rd.F254) <= CONVERT(date,getdate()-8) 
      THEN rd.F64 
END AS Qty2WeeksAgo