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 (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 way to standardize a close-to-ISO date is to use it in an expression that produces an ISO date result. For a date d, any of the following expressions will do:

DATE_ADD(d,INTERVAL 0 DAY)
d + INTERVAL 0 DAY
FROM_DAYS(TO_DAYS(d))
STR_TO_DATE(d,'%Y-%m-%d')

For example, the non-ISO results from the CONCAT() operation can be converted into ISO format several different ways as follows:

mysql>SELECT
    -> CONCAT(YEAR(d),'-',MONTH(d),'-01') AS 'non-ISO',
    -> DATE_ADD(CONCAT(YEAR(d),'-',MONTH(d),'-01'),INTERVAL 0 DAY) AS 'ISO 1',
    -> CONCAT(YEAR(d),'-',MONTH(d),'-01') + INTERVAL 0 DAY AS 'ISO 2',
    -> FROM_DAYS(TO_DAYS(CONCAT(YEAR(d),'-',MONTH(d),'-01'))) AS 'ISO 3',
    -> STR_TO_DATE(CONCAT(YEAR(d),'-',MONTH(d),'-01'),'%Y-%m-%d') AS 'ISO 4'
    -> FROM date_val;
+------------+------------+------------+------------+------------+
| non-ISO    | ISO 1      | ISO 2      | ISO 3      | ISO 4      |
+------------+------------+------------+------------+------------+
| 1864-2-01  | 1864-02-01 | 1864-02-01 | 1864-02-01 | 1864-02-01 |
| 1900-1-01  | 1900-01-01 | 1900-01-01 | 1900-01-01 | 1900-01-01 |
| 1987-3-01  | 1987-03-01 | 1987-03-01 | 1987-03-01 | 1987-03-01 |
| 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 |
| 2000-6-01  | 2000-06-01 | 2000-06-01 | 2000-06-01 | 2000-06-01 |
+------------+------------+------------+------------+------------+

Chapter 10 discusses leap year calculations in the context of date validation.