October 2002
Intermediate to advanced
1024 pages
27h 26m
English
A date is in a format that’s close to but not exactly ISO format.
Canonize the date by passing it to a function that always returns an ISO-format date result.
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 ...