Get date for previous Wednesday to current Tuesday for weekly report

Question

I need to add SQL into a report query that returns the date of the previous Wednesday and the date of the current week Tuesday.

I've been able to get the previous week Tuesday to current week Monday with this:

DATEDIFF(wk,DATEDIFF(wk,7,GETDATE()),1)
DATEDIFF(wk,DATEDIFF(wk,7,GETDATE()),7)

I tried tweaking the numbers from above, but no dice. Not sure if it's because today is the “current Tuesday” so it can’t calculate it, and maybe I just need to try tomorrow.

Answer 1

WITH dates AS (
  SELECt CAST('2023-12-01' as DATE) as D
  UNION ALL
  SELECT DATEADD(DAY,1,D) FROM dates WHERE D<'2024-03-01'),
  dow AS (
  SELECT 1 as x, 'sunday' as dow union all
  SELECT 2 as x, 'monday' as dow union all
  SELECT 3 as x, 'tueday' as dow union all
  SELECT 4 as x, 'wednesday' as dow union all
  SELECT 5 as x, 'thursday' as dow union all
  SELECT 6 as x, 'friday' as dow union all
  SELECT 7 as x, 'saterday' as dow 
 )
SELECT 
   D,
   DATEPART(weekday,D) as weekday,  --  1=sunday; 2=monday; 3=tuesday; ....
   dow
FROM dates
INNER JOIN dow ON DATEPART(weekday,D)=x
WHERE D BETWEEN DATEADD(DAY, 3-DATEPART(weekday,GETDATE())-7, GETDATE())
            AND DATEADD(DAY, 3-DATEPART(weekday,GETDATE()), GETDATE())
ORDER BY D
;

output:

D	weekday	dow
2023-12-13	4	wednesday
2023-12-14	5	thursday
2023-12-15	6	friday
2023-12-16	7	saterday
2023-12-17	1	sunday
2023-12-18	2	monday
2023-12-19	3	tueday

see: DBFIDDLE

• Today is a Tuesday, so 3-DATEPART(weekday,GETDATE()) will get you 0 days added to the current date.
• Tomorrow will be a Wednesday, and get you 3-4, so subtract 1 from the date of tomorrow, which again should be a Tuesday.

Answer 2

With a little help from date arithmetic, we get the following solution.

select
  dateadd(day, wed_day_offset, currently) as prev_wed,
  currently,
  dateadd(day, tue_day_offset, currently) as curr_tue
from
  ( values (getdate()) ) as c(currently)
cross apply
  ( values (datediff(day, 0, c.currently) % 7) ) as wd(week_day)
cross apply
  ( values (-week_day + 1,
            -week_day - 5) ) as do(tue_day_offset,
                                   wed_day_offset)
;

Result.

+--------------------------+--------------------------+--------------------------+
|         prev_wed         |        currently         |         curr_tue         |
+--------------------------+--------------------------+--------------------------+
| 2023-12-13T07:50:35.393Z | 2023-12-20T07:50:35.393Z | 2023-12-19T07:50:35.393Z |
+--------------------------+--------------------------+--------------------------+

SQL Fiddle.