You want to produce a new date from a given date by replacing parts of its values. Or you have the parts of a date or time and want to combine them to produce a date or time value.

You have several options:

The reverse of splitting a date or time value into components is synthesizing a temporal value from its constituent parts. Techniques for date and time synthesis include using composition functions, formatting functions, and string concatenation.

The MAKETIME() function takes component hour, minute, and second values as arguments and combines them to produce a time:

mysql>SELECT MAKETIME(10,30,58), MAKETIME(-5,0,11);
+--------------------+-------------------+
| MAKETIME(10,30,58) | MAKETIME(-5,0,11) |
+--------------------+-------------------+
| 10:30:58           | -05:00:11         |
+--------------------+-------------------+

There is also a MAKEDATE() function, but its arguments are year and day-of-year values:

mysql>SELECT MAKEDATE(2007,60);
+-------------------+
| MAKEDATE(2007,60) |
+-------------------+
| 2007-03-01        |
+-------------------+

I don’t find MAKEDATE() very useful because I’m much more likely to be working with year, month, and day values than year and day-of-year values.

Date synthesis often is performed by beginning with a given date, and then keeping parts that you want to use and replacing the rest. For example, to produce the first day of the month in which a date falls, use DATE_FORMAT() to extract the year and month parts from the date and combine them with a day value of 01:

mysql>SELECT d, DATE_FORMAT(d,'%Y-%m-01') FROM date_val;
+------------+---------------------------+
| d          | DATE_FORMAT(d,'%Y-%m-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                |
+------------+---------------------------+

TIME_FORMAT() can be used in a similar way. 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_FORMAT() 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                          |
| 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                                     |
+------------+------------------------------------------------+

Canonizing Not-Quite-ISO Date Strings 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 its seconds part is 00, extract the hour and minute parts, and then recombine them with CONCAT():

mysql>SELECT t1,
    -> CONCAT(LPAD(HOUR(t1),2,'0'),':',LPAD(MINUTE(t1),2,'0'),':00')
    ->    AS recombined
    -> FROM time_val;
+----------+------------+
| t1       | recombined |
+----------+------------+
| 15:00:00 | 15:00:00   |
| 05:01:30 | 05:01:00   |
| 12:30:20 | 12:30:00   |
+----------+------------+

To produce a combined date-and-time value from separate date and time values, simply concatenate them with a space in between:

mysql>SET @d = '2006-02-28';
mysql> SET @t = '13:10:05';
mysql> SELECT @d, @t, CONCAT(@d,' ',@t);
+------------+----------+---------------------+
| @d         | @t       | CONCAT(@d,' ',@t)   |
+------------+----------+---------------------+
| 2006-02-28 | 13:10:05 | 2006-02-28 13:10:05 |
+------------+----------+---------------------+