Add DATE and TIME fields to get DATETIME field in MySQL?

You can use CONCAT() function to set date and time fields to get DATETIME field.

Let us create a demo table

mysql> create table getDateTimeFieldsDemo
   -> (
   -> ShippingDate date,
   -> ShippingTime time,
   -> Shippingdatetime datetime
   -> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2018-01-21','09:45:34');
Query OK, 1 row affected (0.16 sec)
mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2013-07-26','13:21:20');
Query OK, 1 row affected (0.13 sec)
mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2017-12-31','15:31:40');
Query OK, 1 row affected (0.17 sec)
mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2019-03-07','12:13:34');
Query OK, 1 row affected (0.41 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from getDateTimeFieldsDemo;

The following is the output

+--------------+--------------+------------------+
| ShippingDate | ShippingTime | Shippingdatetime |
+--------------+--------------+------------------+
| 2018-01-21   | 09:45:34     | NULL             |
| 2013-07-26   | 13:21:20     | NULL             |
| 2017-12-31   | 15:31:40     | NULL             |
| 2019-03-07   | 12:13:34     | NULL             |
+--------------+--------------+------------------+
4 rows in set (0.00 sec)

Here is the query to add DATE and TIME fields to get DATETIME field in MySQL

mysql> update getDateTimeFieldsDemo set Shippingdatetime=concat(ShippingDate," ",ShippingTime);
Query OK, 4 rows affected (0.09 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Now check table records once again. The query is as follows −

mysql> select *from getDateTimeFieldsDemo;

The following is the output

+--------------+--------------+---------------------+
| ShippingDate | ShippingTime | Shippingdatetime    |
+--------------+--------------+---------------------+
| 2018-01-21   | 09:45:34     | 2018-01-21 09:45:34 |
| 2013-07-26   | 13:21:20     | 2013-07-26 13:21:20 |
| 2017-12-31   | 15:31:40     | 2017-12-31 15:31:40 |
| 2019-03-07   | 12:13:34     | 2019-03-07 12:13:34 |
+--------------+--------------+---------------------+
4 rows in set (0.00 sec)

Chandu yadav

Published on 01-Apr-2019 09:10:46

• Related Questions & Answers
• Create DATETIME from DATE and TIME in MySQL?
• How to part DATE and TIME from DATETIME in MySQL?
• How to convert JS date time to MySQL datetime?
• How to update date of datetime field with MySQL?
• MySQL query to get current datetime and only current date
• How to add a day to datetime field in MySQL query?
• Compare DATE string with string from MySQL DATETIME field?
• Add a single day to datetime field with MySQL INTERVAL
• Get only the date from datetime in MySQL?
• Extracting only date from datetime field in MySQL and assigning it to PHP variable?
• How to split the datetime column into date and time and compare individually in MySQL?
• Comparing dates in MySQL ignoring time portion of a DateTime field?
• Insert datetime into another datetime field in MySQL?
• MySQL Datetime to add days?
• How can I add one day to DATETIME field in MySQL query?