# Thread: IF statement in WHERE clause

1. Registered User
Join Date
Mar 2007
Posts
212

## Unanswered: IF statement in WHERE clause

HI,

Is it possible to use an IF statement in the WHERE clause?

Basically I have query that lists orders over the last 5 days :
Code:
```WHERE (order_date = @current_date OR
order_date = DATEADD(dd, @current_date - 1) OR
order_date = DATEADD(dd, @current_date - 2) OR
order_date = DATEADD(dd, @current_date - 3) OR
order_date = DATEADD(dd, @current_date - 4)  )```
How can I add an IF statement so that if @current_date is Monday then only lists orders for the Monday of that week. If @current_date is Tuesday then only list orders for the Monday and Tuesday of that week etc. In other words I only want to list orders for the days in the same week as @current_date.

I've tried using a CASE statement but it comes up with an error message about the OR statement in the query:
Code:
```WHERE order_date =
CASE WHEN DATEPART(dw, @current_date) = 1 THEN
@current_date
WHEN DATEPART(dw, @current_date) = 2 THEN
@current_date OR
order_date = DATEADD(dd, @current_date - 1)
WHEN DATEPART(dw, @current_date) = 3 THEN
@current_date OR
order_date = DATEADD(dd, @current_date - 1) OR
order_date = DATEADD(dd, @current_date - 2)
WHEN DATEPART(dw, @current_date) = 4 THEN
@current_date OR
order_date = DATEADD(dd, @current_date - 1) OR
order_date = DATEADD(dd, @current_date - 2) OR
order_date = DATEADD(dd, @current_date - 3)
WHEN DATEPART(dw, @current_date) = 5 THEN
@current_date OR
order_date = DATEADD(dd, @current_date - 1) OR
order_date = DATEADD(dd, @current_date - 2) OR
order_date = DATEADD(dd, @current_date - 3) OR
order_date = DATEADD(dd, @current_date - 4)
END```
Any other way to acheive this would be appreciated.

Note: for the above query I've set DATEFIRST to 1 so that Monday is the first day of the week.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```WHERE order_date BETWEEN DateAdd(wk, DateDiff(wk, 0, @current_date), 0)
AND @current_date```
-PatP

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Pat - what's changed re. your opinion of implicit conversion of integers to dates?

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I post as the members of the forum post, I code as I code for my personal and professional code. In other words my opinion hasn't changed, I think using zero as a substitute for an arbitrary date is an abomination but I do it here because that's what the consensus for coding seems to be here.

In my own code, I use explicit dates. I think that everyone ought to do that. I'm still willing to try to post code that looks the way that DBForums users appear to want their code formatted.

When in Rome...

-PatP

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Since 0 is a simply constant, and a magic one at that, I took your views on board and have now switched to coding the requirement like this:
Code:
`DateAdd(wk, DateDiff(wk, '19000101', @current_date), '19000101')`

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
That's pretty much how I'd code it, but I use the ISO formatted dates like '1900-01-01' but you and I see eye-to-eye on this. Based on the code that I see posted, the posters here at DBForums seem to use and expect the zero so when I post code here I try to do the same. I feel that it is poor coding practice, it gives me the willies every time I write it because I know deep down that it is wrong, but it is what I usually see here so it is what I try to post.

There are a lot of things that I do as regular coding practice that would probably freak people out. I almost always put constants on the left of comparisions, I always use UTC time and date values, I have a strong preference for GUIDs... I have a strong coding style that has developed over decades of coding. Every habit has a reason, and I remember the vast majority of those reasons. I don't make everybody code "my way", that would be rude.

-PatP

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
I don't use the date-only-ISO formatted date because it can fail for a British connection. '1900-01-13' would have to instead be '1900-01-13T00:00:00' which is a PITA, however in principle I agree with its usage!

We will differ on your last point though. I think it is the height of rudeness when other people do not subscribe to my coding style.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•