Database Reference
In-Depth Information
See Also
Recipe 6.18 discusses how to calculate month lengths from within a program without
using SQL. (The trick is that you must account for leap years.)
6.15. Calculating Dates by Substring Replacement
Problem
Given a date, you want to produce another date from it when you know that the two
dates share some components in common.
Solution
Treat a date or time value as a string, and perform direct replacement on parts of the
string.
Discussion
In some cases, you can use substring replacement to calculate dates without performing
any date arithmetic. For example, a string operation produces the first-of-month value
for a given date by replacing the day component with 01 . You can do this either with
DATE_FORMAT() or with CONCAT() :
mysql> SELECT d,
-> DATE_FORMAT(d,'%Y-%m-01') AS '1st of month A',
-> CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') AS '1st of month B'
-> FROM date_val;
+------------+----------------+----------------+
| d | 1st of month A | 1st of month B |
+------------+----------------+----------------+
| 1864-02-28 | 1864-02-01 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 | 2000-06-01 |
| 2017-03-16 | 2017-03-01 | 2017-03-01 |
+------------+----------------+----------------+
The string replacement technique can also produce dates with a specific position within
the calendar year. For New Year's Day (January 1), replace the month and day with 01 ;
for Christmas, replace the month and day with 12 and 25 :
mysql> SELECT d,
-> DATE_FORMAT(d,'%Y-01-01') AS 'New Year''s A',
-> CONCAT(YEAR(d),'-01-01') AS 'New Year''s B',
-> DATE_FORMAT(d,'%Y-12-25') AS 'Christmas A',
-> CONCAT(YEAR(d),'-12-25') AS 'Christmas B'
-> FROM date_val;
Search WWH ::




Custom Search