Working with Dates and Times - MySQL - page 222

1. Shift the reference date back by its DAYOFWEEK() value, which always produces the
date for the Saturday preceding the week.
2. Shift the Saturday date by one day to reach the Sunday date, by two days to reach
the Monday date, and so forth.
In SQL, those operations can be expressed as follows for a date d , where n is 1 through
7 to produce the dates for Sunday through Saturday:
DATE_ADD(DATE_SUB(d,INTERVAL DAYOFWEEK(d) DAY),INTERVAL n DAY)
That expression splits the “shift back to Saturday” and “shift forward” phases into sep‐
arate operations, but because the intervals for both DATE_SUB() and DATE_ADD() are in
days, the expression can be simplified into a single DATE_ADD() call:
DATE_ADD(d,INTERVAL n -DAYOFWEEK(d) DAY)
Applying this formula to the dates in our date_val table, using an n of 1 for Sunday and
7 for Saturday to find the first and last days of the week, yields this result:
mysql> SELECT d, DAYNAME(d) AS day,
-> DATE_ADD(d,INTERVAL 1-DAYOFWEEK(d) DAY) AS Sunday,
-> DATE_ADD(d,INTERVAL 7-DAYOFWEEK(d) DAY) AS Saturday
-> FROM date_val;
+------------+----------+------------+------------+
| d | day | Sunday | Saturday |
+------------+----------+------------+------------+
| 1864-02-28 | Sunday | 1864-02-28 | 1864-03-05 |
| 1900-01-15 | Monday | 1900-01-14 | 1900-01-20 |
| 1999-12-31 | Friday | 1999-12-26 | 2000-01-01 |
| 2000-06-04 | Sunday | 2000-06-04 | 2000-06-10 |
| 2017-03-16 | Thursday | 2017-03-12 | 2017-03-18 |
+------------+----------+------------+------------+
To determine the date of some weekday in a week relative to that of the target date,
modify the preceding procedure a bit. First, determine the date of the desired weekday
in the target date. Then shift the result into the desired week.
Calculating the date for a day of the week in some other week is a problem that breaks
down into a day-within-week shift (using the formula just given) plus a week shift. These
operations can be done in either order because the amount of shift within the week is
the same whether or not you shift the reference date into a different week first. For
example, to calculate Wednesday of a week by the preceding formula, n is 4. To compute
the date for Wednesday two weeks ago, you can perform the day-within-week shift first,
like this:
mysql> SET @target =
-> DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 4-DAYOFWEEK(CURDATE()) DAY),
-> INTERVAL 14 DAY);
mysql> SELECT CURDATE(), @target, DAYNAME(@target);