If today is friday then extract data 7 to 9 days from today, if not 7 days

Question

If today is Friday, I would like to get data 7 to 9 days from now, if not 7 days. I am using SQL Server.

Before:

id	class	startdate
1	English	2020-12-21 00:00:00.000
2	English	2020-12-22 00:00:00.000
3	Math	2020-12-21 00:00:00.000
4	English	2020-12-27 00:00:00.000
5	Math	2020-12-27 00:00:00.000

If today is 14/12/2020 - Monday, it will produce the result below:

id	class	startdate
1	English	2020-12-21 00:00:00.000
3	Math	2020-12-21 00:00:00.000

I tried the following, but it doesn't work.

select id, class, startdate
from class
where case datepart(w, GETDATE())
    when 6 then startdate between DateAdd(dd,+9,GETDATE() ) and DateAdd(dd,+10,GETDATE()))
    else startdate between DateAdd(dd,+7,GETDATE() ) and DateAdd(dd,+8,GETDATE()))
end
order by startdate

Does this answer your question? [Count work days between two dates](https://stackoverflow.com/questions/252519/count-work-days-between-two-dates) Standard approach is to create a 'working days' or 'holidays' table (or a 'dates' table with a working day/non-working day flag) this allows you to extend your solution to public holidays etc. — JeffUK, Dec 14 '20 at 09:38
Aside: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. — HABO, Dec 14 '20 at 19:47

score 2 · Answer 1 · answered Dec 14 '20 at 12:59

OR usually prevents index usage anyway, so you can do:

where datediff(day, getdate(), startdate)
          between 7 and
                  (case when datepart(weekday, GETDATE()) = 6 then 9 else 7 end)

If you are interested in performance, then use two separate queries:

select id, class, startdate
from class
where datepart(weekday, getdate()) = 6 and
      startdate >= dateadd(day, 7, convert(date, getdate())) and
      startdate <= dateadd(day, 9, convert(date, getdate())) 
union all
select id, class, startdate
from class
where datepart(weekday, getdate()) <> 6 and
      startdate = dateadd(day, 7, convert(date, getdate()));

SQL Server should find this easier to optimize.

Giorgos Betsos · Accepted Answer · 2020-12-14T10:11:26.013

You can't use a CASE expression like this in SQL Server. Try this instead:

select id, class, startdate
from class
cross apply 
(
   select case datepart(dw, GETDATE())
           when 6 then 9
           else 7
         end
) t(days)
where startdate between dateadd(dd, t.days ,GETDATE()) an dateadd(dd,t.days+1,GETDATE())
order by startdate

Demo here

To answer to your comment:

select id, class, startdate
from class
cross apply 
(
select 
  case datepart(dw, GETDATE())
           when 6 then 9
           else 7
         end,
  case datepart(dw, GETDATE())
           when 6 then 11
           else 8
         end  
) t(days1, days2)
where startdate between dateadd(dd, t.days1 ,GETDATE()) and dateadd(dd,t.days2,GETDATE())
order by startdate

Hi, thanks for helping out. How about if Friday, it will extract out 7th to 9th day of data -- I missed out this condition. — poppp, Dec 14 '20 at 09:59

score 1 · Answer 3 · answered Dec 14 '20 at 10:04

If you have datetime with actual hh:mi:ss in it - just change it to date and and it would be much easier

select id, 
       class, 
       startdate
from class
where (datepart(w, GETDATE()) = 6 and
    startdate = CAST(DateAdd(dd,+9,GETDATE()) as date) 
) 
    or (datepart(w, GETDATE()) != 6 and
    startdate = CAST(DateAdd(dd,+7,GETDATE()) as date) 
       )
order by startdate

If it is not an option try this:

select id, 
       class, 
       startdate
from class
where (datepart(w, GETDATE()) = 6 and
    startdate between DateAdd(dd,+9,CAST(GETDATE() as date)) 
                  and DATEADD(second,-1,datediff(dd,0,DateAdd(dd,+9,CAST(GETDATE() as date)))+1)
    ) 
    or (datepart(w, GETDATE()) != 6 and
    startdate between DateAdd(dd,+7,CAST(GETDATE() as date)) 
                   -- to get YYYY-MM-DD 00:00:00
                  and DATEADD(second,-1,datediff(dd,0,DateAdd(dd,+7,CAST(GETDATE() as date)))+1)
                  -- to get YYYY-MM-DD 23:59:59
       )
order by startdate

score 0 · Answer 4 · answered Dec 15 '20 at 03:31

0

This has probably been made more complicated than it should. Create two parameters @start and @end If today is Friday set parameters as appropriate else if it is not set as needed. Use parameters in your query

answered Dec 15 '20 at 03:31

openball

21
4

If today is friday then extract data 7 to 9 days from today, if not 7 days

4 Answers4