October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You have the parts of a date or time and want to combine them to produce a date or time value.
Put the parts together using CONCAT( ).
Another way to construct temporal values is to use date-part
extraction functions in conjunction with CONCAT( ). However, this method often is messier than the
DATE_FORMAT( ) technique discussed in Recipe 5.8—and it sometimes yields slightly
different results:
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 | +------------+------------------------------------+
Note that the month values in some of these dates have only a single
digit. To ensure that the month has two digits—as required for
ISO format—use LPAD( ) to add a
leading zero as necessary:
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 | +------------+------------------------------------------------+ ...