Solved

# Using Getdate() -1 if today is Tuesday to Friday and getdate() -3 if day is Monday

Posted on 2012-09-18
594 Views
Hi,
I want to return a query set that depends on which day of the week it is
If it is Tuesday to Friday then return getdate() -1 to give the previous days result set
If it is Monday then return getdate() - 3 to give me Fridays result set

Pseudo
select * from resultset
where case when dateis = (Tuesday to Friday) then getdate() -1
else when dateis = (Friday) then getdate() - 3
end
0
Question by:owenja

LVL 17

Expert Comment

select * from resultset
CASE
WHEN DATEPART(dw, @Date) + @@DATEFIRST) % 7 = 2 THEN DATEADD(day,-3,getdate())
WHEN DATEPART(dw, @Date) + @@DATEFIRST) % 7 > 2 AND DATEPART(dw, @Date) + @@DATEFIRST) % 7 <=6 THEN  DATEADD(day,-1,getdate())
END
0

LVL 12

Expert Comment

select * from resultset
where  dateis =  case when datepart(dw,getdate()) between 3 and 5 then getdate() -1
when datepart(dw,getdate()) =6 then getdate() - 3 end
0

LVL 17

Expert Comment

select * from resultset
CASE
WHEN DATEPART(dw, getdate()) + @@DATEFIRST) % 7 = 2 THEN DATEADD(day,-3,getdate())
WHEN DATEPART(dw, getdate()) + @@DATEFIRST) % 7 > 2 AND DATEPART(dw, @Date) + @@DATEFIRST) % 7 <=6 THEN  DATEADD(day,-1,getdate())
END
0

LVL 12

Accepted Solution

slightly change in logic

select * from resultset
where  dateis =  case when datepart(dw,getdate()) between 3 and 6 then getdate() -1
when datepart(dw,getdate()) =2 then getdate() - 3 end
0

LVL 17

Expert Comment

select * from resultset
WHERE [your_date] =
CASE
WHEN (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7 = 2 THEN DATEADD(day,-3,getdate())
WHEN (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7 > 2 AND (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7 < 7 THEN  DATEADD(day,-1,getdate())
END
0

## Featured Post

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.