Your title said 13 Wednesdays, your text said 3 Wednesdays. I'm going with the text.
We count days base 7, weeks base 52, months base 12. In some countries the week begins on Sunday, in others Monday. Weekday numbers are counted base 0 in some places, base 1 in others. Date arithmetic is awkward.
Use whatever help SQL can provide. DayOfWeek() returns the day number of the week with Sunday=1, AddDate() adds and subtracts dates.
There are three cases: today is the same weekday as the target date, before it, or after it. They can be collapsed to one if we calculate in terms of the
difference between the dayOfWeek of today and the dayOfWeek of the desired date (Wednesday=4).
set @N=3; -- NO OF WEEKS BACK
set @d=curdate();
set @daynum=4; -- TARGET DAY=WEDNESDAY
set @offs = if( dayofweek(@d) > @daynum, 0, 1 );
select
@d as startdate,
dayname(@d) as dayname,
@d - interval 7*(@N+@offs) - ( 7 - ( dayOfWeek(@d) - 4) ) day as 3WedsAgo;
+------------+------------+
| today | 3WedsAgo |
+------------+------------+
| 2018-11-01 | 2018-10-17 |
+------------+------------+
What if we'd calculated it earlier in the week, say yesterday?
set @d=curdate() - interval 1 day;
set @offs = if( dayofweek(@d) > @daynum, 0, 1 );
select
@d as startdate,
dayname(@d) as dayname,
@d - interval 7*(@N+@offs) - ( 7 - ( dayOfWeek(@d) - 4) ) day as 3WedsAgo;
+------------+-----------+------------+
| startdate | dayname | 3WedsAgo |
+------------+-----------+------------+
| 2018-10-31 | Wednesday | 2018-10-10 |
+------------+-----------+------------+
Edited 1 time(s). Last edit at 11/01/2018 01:54PM by Peter Brawley.