SQL query with case datepart

Question

I would like to get the data which will exclude weekends from result based on a bind variable value. Somehow I am not able to get this query to run.

select *  from tablename a
WHERE a.date >= '2015-04-13'
AND a.date <= '2015-04-21'
AND CASE  WHEN :1 = 'Y' THEN ((DATEPART(dw, a.date) + @@DATEFIRST) % 7) NOT IN (0, 1) END

I am getting the following error : Incorrect syntax near the keyword 'NOT'.

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Oct 15 '15 at 06:49

score 0 · Answer 1 · answered Oct 15 '15 at 06:29

Use a case expression here over-complicates things, IMHO. You have two situations:

If the bind variables is Y, you need to exclude weekends.
If it isn't, you want to include them.

This logic can be achieved with a much simpler (again, IMHO) usage of the or logical operator:

SELECT *
FROM   tablename a
WHERE  a.date >= '2015-04-13' AND 
       a.date <= '2015-04-21' AND
       (:1 != 'Y' OR ((DATEPART(dw, a.date) + @@DATEFIRST) % 7) NOT IN (0, 1))

SQL query with case datepart

1 Answers1