asked on

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

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

Barry Cunney

select * from resultset
WHERE your date =
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

Saurabh Bhadauria

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

Barry Cunney

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, @Date) + @@DATEFIRST) % 7 <=6 THEN DATEADD(day,-1,getdate())
END

ASKER CERTIFIED SOLUTION

Saurabh Bhadauria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Barry Cunney

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