Working with Dates and Times - MySQL - page 200

| 2017-03-16 | 2017-03-01 |
+------------+---------------------------+
TIME_FORMAT() can be used similarly. The following example produces time values that
have the seconds part set to 00 :
mysql> SELECT t1, TIME_FORMAT(t1,'%H:%i:00') FROM time_val;
+----------+----------------------------+
| t1 | TIME_FORMAT(t1,'%H:%i:00') |
+----------+----------------------------+
| 15:00:00 | 15:00:00 |
| 05:01:30 | 05:01:00 |
| 12:30:20 | 12:30:00 |
+----------+----------------------------+
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_FOR
MAT() technique just discussed, 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 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-6-01 |
| 2017-03-16 | 2017-3-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 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
| 2017-03-16 | 2017-03-01 |
+------------+------------------------------------------------+
Recipe 6.19 shows other ways to solve the problem of producing ISO dates from not-
quite-ISO dates.
TIME values can be produced from hours, minutes, and seconds values using methods
analogous to those for creating DATE values. For example, to change a TIME value so that