Canonizing Not-Quite-ISO Date Strings
Problem
A date is in a format that’s close to but not exactly ISO format.
Solution
Canonize the date by passing it to a function that always returns an ISO-format date result.
Discussion
Earlier in the chapter (Recipe 5.9), we ran into the problem that synthesizing
dates with CONCAT( )
may produce values that are
not quite in ISO format. For example, the following query produces
first-of-month values in which the month part may have only a single
digit:
mysql> SELECT d, CONCAT(YEAR(d),'-',MONTH(d),'-01') FROM date_val;
+------------+------------------------------------+
| d | CONCAT(YEAR(d),'-',MONTH(d),'-01') |
+------------+------------------------------------+
| 1864-02-28 | 1864-2-01 |
| 1900-01-15 | 1900-1-01 |
| 1987-03-05 | 1987-3-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-6-01 |
+------------+------------------------------------+
In that section, a technique using LPAD( )
was shown for making
sure the month values have two digits.
mysql> SELECT d, CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') FROM date_val;
+------------+------------------------------------------------+
| d | CONCAT(YEAR(d),'-',LPAD(MONTH(d),2,'0'),'-01') |
+------------+------------------------------------------------+
| 1864-02-28 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 |
| 1987-03-05 | 1987-03-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
+------------+------------------------------------------------+
Another way to standardize a close-to-ISO ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.