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; +------------+------------------------------------------------+ | 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))
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 | +------------+------------+------------+------------+------------+
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References