Recipe 6.18. Canonizing Not-Quite-ISO Date Strings
ProblemA date is in a format that's close to but not exactly ISO format. SolutionCanonize the date by passing it to a function that always returns an ISO-format date result. DiscussionEarlier in the chapter (Section 6.7), 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 | +------------+------------+------------+------------+------------+ See AlsoChapter 10 discusses leap year calculations in the context of date validation. |
