MySQL Cookbook, 2nd Edition

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 (Synthesizing Dates or Times from Component Values), we ran into the problem that synthesizing dates with CONCAT() may produce values that are not quite in ISO format. For example, the following statement 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 ...