# Canonizing Not-Quite-ISO Date Strings

5.19.1 Problem

A date is in a format that's close to but not exactly ISO format.

5.19.2 Solution

Canonize the date by passing it to a function that always returns an ISO-format date result.

5.19.3 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;
+------------+------------------------------------------------+
+------------+------------------------------------------------+
| 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))```

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

```mysql> SELECT d,
-> CONCAT(YEAR(d),'-',MONTH(d),'-01') AS 'non-ISO',
-> DATE_ADD(CONCAT(YEAR(d),'-',MONTH(d),'-01'),INTERVAL 0 DAY) AS method1,
-> CONCAT(YEAR(d),'-',MONTH(d),'-01') + INTERVAL 0 DAY AS method2,
-> FROM_DAYS(TO_DAYS(CONCAT(YEAR(d),'-',MONTH(d),'-01'))) AS method3
-> FROM date_val;
+------------+------------+------------+------------+------------+
| d | non-ISO | method1 | method2 | method3 |
+------------+------------+------------+------------+------------+
| 1864-02-28 | 1864-2-01 | 1864-02-01 | 1864-02-01 | 1864-02-01 |
| 1900-01-15 | 1900-1-01 | 1900-01-01 | 1900-01-01 | 1900-01-01 |
| 1987-03-05 | 1987-3-01 | 1987-03-01 | 1987-03-01 | 1987-03-01 |
| 1999-12-31 | 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 |
| 2000-06-04 | 2000-6-01 | 2000-06-01 | 2000-06-01 | 2000-06-01 |
+------------+------------+------------+------------+------------+```

MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon