Database Reference
In-Depth Information
DAYNAME()
is often useful in conjunction with other date-related techniques. For ex‐
ample, to determine the day of the week for the first of the month, use the first-of-month
expression from
Recipe 6.14
as the argument to
DAYNAME()
:
mysql>
SET @d = CURDATE();
mysql>
SET @first = DATE_SUB(@d,INTERVAL DAYOFMONTH(@d)-1 DAY);
mysql>
SELECT @d AS 'starting date',
->
@first AS '1st of month date',
->
DAYNAME(@first) AS '1st of month day';
+---------------+-------------------+------------------+
| starting date | 1st of month date | 1st of month day |
+---------------+-------------------+------------------+
| 2014-02-20 | 2014-02-01 | Saturday |
+---------------+-------------------+------------------+
6.17. Finding Dates for Any Weekday of a Given Week
Problem
You want to compute the date of some weekday for the week in which a given date lies.
Suppose that you want to know the date of the Tuesday that falls in the same week as
2014-07-09
.
Solution
This 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.
Discussion
This 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
2014-07-09
. To determine
the date for 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
2014-07-10
, but if it's a Wed‐
nesday, you subtract a day to produce
2014-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:
