Database Reference
In-Depth Information
+------------+--------------+--------------+-------------+-------------+
| d | New Year's A | New Year's B | Christmas A | Christmas B |
+------------+--------------+--------------+-------------+-------------+
| 1864-02-28 | 1864-01-01 | 1864-01-01 | 1864-12-25 | 1864-12-25 |
| 1900-01-15 | 1900-01-01 | 1900-01-01 | 1900-12-25 | 1900-12-25 |
| 1999-12-31 | 1999-01-01 | 1999-01-01 | 1999-12-25 | 1999-12-25 |
| 2000-06-04 | 2000-01-01 | 2000-01-01 | 2000-12-25 | 2000-12-25 |
| 2017-03-16 | 2017-01-01 | 2017-01-01 | 2017-12-25 | 2017-12-25 |
+------------+--------------+--------------+-------------+-------------+
To perform the same operation for the target calendar day in other years, combine string
replacement with date shifting. The following statement shows two ways to determine
the date for Christmas two years hence. The first method finds Christmas for this year,
and then shifts it two years forward. The second shifts the current date forward two
years, and then finds Christmas in the resulting year:
mysql>
SELECT CURDATE(),
->
DATE_ADD(DATE_FORMAT(CURDATE(),'%Y-12-25'),INTERVAL 2 YEAR)
->
AS 'Christmas A',
->
DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 2 YEAR),'%Y-12-25')
->
AS 'Christmas B';
+------------+-------------+-------------+
| CURDATE() | Christmas A | Christmas B |
+------------+-------------+-------------+
| 2014-02-20 | 2016-12-25 | 2016-12-25 |
+------------+-------------+-------------+
6.16. Finding the Day of the Week for a Date
Problem
You want to know the day of the week on which a date falls.
Solution
Use the
DAYNAME()
function.
Discussion
To determine the name of the day of the week for a given date, use
DAYNAME()
:
mysql>
SELECT CURDATE(), DAYNAME(CURDATE());
+------------+--------------------+
| CURDATE() | DAYNAME(CURDATE()) |
+------------+--------------------+
| 2014-02-20 | Thursday |
+------------+--------------------+
