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.

Only tag the DBMS you are using ! Either MySQL or SQL-Server. Do not include links. Instead edit your question and include your error statement in the question. — Tony
– Tony, Commented Feb 6, 2019 at 13:53

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-06 13:57:58Z

2

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

answered Feb 6, 2019 at 13:57

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stanislovas Kalašnikovas · Accepted Answer · 2019-02-06 14:02:26Z

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

edited Feb 6, 2019 at 14:02

answered Feb 6, 2019 at 13:57

Stanislovas Kalašnikovas

9,0735 gold badges36 silver badges60 bronze badges

Comments

Muhammad Waheed · Accepted Answer · 2019-02-06 13:56:35Z

0

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

answered Feb 6, 2019 at 13:56

Muhammad Waheed

1,0882 gold badges16 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL WHEN function - How to fix Syntax Error?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related