Concatenate date and time from separate columns into a single column in MySQL

For this, concatenate both the date and time using CONCAT() function. Let us first create a table −

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

Insert some records in the table using insert command −

mysql> insert into DemoTable(ShippingDate,ShippingTime) values('2019-01-10', '10:40:20');
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable(ShippingDate,ShippingTime) values('2019-06-14', '04:00:10');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

+--------------+--------------+------------------+
| ShippingDate | ShippingTime | ShippingDatetime |
+--------------+--------------+------------------+
| 2019-01-10   | 10:40:20     | NULL             |
| 2019-06-14   | 04:00:10     | NULL             |
+--------------+--------------+------------------+
2 rows in set (0.00 sec)

Here is the query to concatenate date and time from separate columns into a single column in MySQL −

mysql> update DemoTable set ShippingDatetime=concat(ShippingDate,' ',ShippingTime);
Query OK, 2 rows affected (0.11 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Let us check table records once again −

mysql> select *from DemoTable;

Output

+--------------+--------------+---------------------+
| ShippingDate | ShippingTime | ShippingDatetime    |
+--------------+--------------+---------------------+
| 2019-01-10   | 10:40:20     | 2019-01-10 10:40:20 |
| 2019-06-14   | 04:00:10     | 2019-06-14 04:00:10 |
+--------------+--------------+---------------------+
2 rows in set (0.00 sec)

Rama Giri

Published on 10-Jul-2019 11:35:22

• Related Questions & Answers
• Concatenate the column values with separate text in MySQL and display in a single column
• Combine date and time column into a timestamp in MySQL?
• How to separate last name and first names in single column into two new columns in MySQL?
• Concatenate all the columns in a single new column with MySQL
• Write a program to separate date and time from the datetime column in Python Pandas
• Concatenate multiple rows and columns in a single row with MySQL
• Retrieve MIN and MAX date in a single MySQL query from a column with date values
• Display the sum of positive and negative values from a column in separate columns with MySQL
• Fetch a single ordered date from a column with MySQL LIMIT
• Concatenate columns from different tables in MySQL
• Select distinct values from three columns and display in a single column with MySQL
• Get the minimum and maximum value from a VARCHAR column and display the result in separate MySQL columns?
• Select multiple columns and display in a single column in MySQL?
• How to split the datetime column into date and time and compare individually in MySQL?
• How to concatenate all values of a single column in MySQL?