Database Reference
In-Depth Information
+------------+------------+------------------+
| CURDATE() | @target | DAYNAME(@target) |
+------------+------------+------------------+
| 2014-02-20 | 2014-02-05 | 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) |
+------------+------------+------------------+
| 2014-02-20 | 2014-02-05 | Wednesday |
+------------+------------+------------------+
Some applications need to determine dates such as the
n
-th instance of particular week‐
days. For example, to administer a payroll for which paydays are the second and fourth
Thursdays of each month, you must 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 2014. 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 Thurs‐
days:
my
$year
=
2014
;
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 program produces this output:
