CODEWITHSUNDEEP
sqlsql-servert-sql
Cinublabla
Cinublabla

Reputation: 13

Issue with BETWEEN in CASE statement in WHERE clause

I'm trying to schedule automatic task which will pull data based on the current month. The tricky part is that it needs to pull a weird date (e.g. from 01.15 to 02.17) every month. I've tried the below but it's saying that there's an issue with my BETWEEN statement.

WHERE
 CASE WHEN
 DATEADD(MONTH, DATEDIFF(MONTH, 0,  GETDATE() ), 0) = '2017-05'
THEN 
CAST(<here_is_the_date_column>) AS date) BETWEEN '2017-04-23' AND '2017-05-23'

Upvotes: 0

Views: 155

Answers (2)

Rahul
Rahul

Reputation: 77934

You can use a compound logical expression instead like

WHERE DATEADD(MONTH, DATEDIFF(MONTH, 0,  GETDATE() ), 0) = '2017-05-01'
AND 
CAST(<here_is_the_date_column>) AS date) BETWEEN '2017-04-23' AND '2017-05-23'

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82564

You try to use the case expression as flow control (like an if..else), but case is an expression, meaning it returns a single scalar value, and it can't be used like this. It's even stated in the case MSDN page:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

What you need is and and or:

WHERE  
(
   DATEADD(MONTH, DATEDIFF(MONTH, 0,  GETDATE() ), 0) = '2017-05-01'
   AND CAST(<here_is_the_date_column>) AS date) BETWEEN '2017-04-23' AND '2017-05-23'
)  
OR -- Here comes the part you would use for the `else` part of the case statement.

Upvotes: 1

Related Questions