MySQL Forums
Forum List  »  Newbie

Re: 3 Wednesdays ago
Posted by: Peter Brawley
Date: November 01, 2018 01:51PM

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.

Options: ReplyQuote


Subject
Written By
Posted
November 01, 2018 09:52AM
Re: 3 Wednesdays ago
November 01, 2018 01:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.