Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

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
0
owenja
Asked:
owenja
  • 3
  • 2
1 Solution
 
Barry CunneyCommented:
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
0
 
Saurabh BhadauriaCommented:
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
 
Barry CunneyCommented:
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
0
 
Saurabh BhadauriaCommented:
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
 
Barry CunneyCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now