Recipe 6.16. Finding Dates for Any Weekday of a Given Week
ProblemYou want to compute the date of some weekday for the week in which a given date lies. For example, suppose that you want to know the date of the Tuesday that falls in the same week as 2006-07-09. SolutionThis is an application of date shifting. Figure out the number of days between the starting weekday of the given date and the desired day, and shift the date by that many days. DiscussionThis section and the next describe how to convert one date to another when the target date is specified in terms of days of the week. To solve such problems, you need to know day-of-week values. Suppose you begin with a target date of 2006-07-09. If you want to know what date it is on Tuesday of the week in which that date lies, the calculation depends on what weekday it is. If it's a Monday, you add a day to produce 2006-07-10, but if it's a Wednesday, you subtract a day to produce 2006-07-08. MySQL provides two functions that are useful here. DAYOFWEEK( ) TReats Sunday as the first day of the week and returns 1 through 7 for Sunday through Saturday. WEEKDAY( ) TReats Monday as the first day of the week and returns 0 through 6 for Monday through Sunday. (The examples shown here use DAYOFWEEK( ).) Another kind of day-of-week operation involves determining the name of the day. DAYNAME( ) can be used for that. Calculations that determine one day of the week from another depend on the day you start from as well as the day you want to reach. I find it easiest to shift the reference date first to a known point relative to the beginning of the week, and then shift forward:
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 separate 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) If we apply 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, we get 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 | | 1987-03-05 | Thursday | 1987-03-01 | 1987-03-07 | | 1999-12-31 | Friday | 1999-12-26 | 2000-01-01 | | 2000-06-04 | Sunday | 2000-06-04 | 2000-06-10 | +------------+----------+------------+------------+ If you want to know 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); +------------+------------+------------------+ | CURDATE() | @target | DAYNAME(@target) | +------------+------------+------------------+ | 2006-05-22 | 2006-05-10 | Wednesday | +------------+------------+------------------+ Or you can perform the week shift first: mysql> SET @target = -> DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 14 DAY), -> INTERVAL 4-DAYOFWEEK(CURDATE()) DAY); mysql> SELECT CURDATE(), @target, DAYNAME(@target); +------------+------------+------------------+ | CURDATE() | @target | DAYNAME(@target) | +------------+------------+------------------+ | 2006-05-22 | 2006-05-10 | Wednesday | +------------+------------+------------------+ Some applications need to determine dates such as the n-th instance of particular weekdays. For example, if you administer a payroll where paydays are the second and fourth Thursdays of each month, you'd need to know what those dates are. One way to do this for any given month is to begin with the first-of-month date and shift it forward. It's easy enough to shift the date to the Thursday in that week; the trick is to figure out how many weeks forward to shift the result to reach the second and fourth Thursdays. If the first of the month occurs on any day from Sunday through Thursday, you shift forward one week to reach the second Thursday. If the first of the month occurs on Friday or later, you shift forward by two weeks. The fourth Thursday is of course two weeks after that. The following Perl code implements this logic to find all paydays in the year 2007. It runs a loop that constructs the first-of-month date for the months of the year. For each month, it issues a statement that determines the dates of the second and fourth Thursdays: my $year = 2007; print "MM/CCYY 2nd Thursday 4th Thursday\n"; foreach my $month (1..12) { my $first = sprintf ("%04d-%02d-01", $year, $month); my ($thu2, $thu4) = $dbh->selectrow_array (qq{ SELECT DATE_ADD( DATE_ADD(?,INTERVAL 5-DAYOFWEEK(?) DAY), INTERVAL IF(DAYOFWEEK(?) <= 5, 7, 14) DAY), DATE_ADD( DATE_ADD(?,INTERVAL 5-DAYOFWEEK(?) DAY), INTERVAL IF(DAYOFWEEK(?) <= 5, 21, 28) DAY) }, undef, $first, $first, $first, $first, $first, $first); printf "%02d/%04d %s %s\n", $month, $year, $thu2, $thu4; } The output from the program looks like this: MM/CCYY 2nd Thursday 4th Thursday 01/2007 2007-01-11 2007-01-25 02/2007 2007-02-08 2007-02-22 03/2007 2007-03-08 2007-03-22 04/2007 2007-04-12 2007-04-26 05/2007 2007-05-10 2007-05-24 06/2007 2007-06-14 2007-06-28 07/2007 2007-07-12 2007-07-26 08/2007 2007-08-09 2007-08-23 09/2007 2007-09-13 2007-09-27 10/2007 2007-10-11 2007-10-25 11/2007 2007-11-08 2007-11-22 12/2007 2007-12-13 2007-12-27 |
