How to separate date and time in DATETIME in MySQL?

To separate DATE and TIME from DATETIME, you can use MySQL's DATE_FORMAT() method. The syntax is as follows -

SELECT DATE_FORMAT(yourColumnName, '%Y-%m-%d') VariableName, DATE_FORMAT(yourColumnName,'%H:%i:%s') VariableName from yourTableName;

In order to understand the above method DATE_FORMAT(), let us create a table with data type "datetime".

Create a table -

mysql> create table DateAndTimePartDemo
   -> (
   -> YourDateandtime datetime
   -> );
Query OK, 0 rows affected (0.56 sec)

Now, I am using now() to insert the current date and time. The query is as follows −

mysql> insert into DateAndTimePartDemo values(now());
Query OK, 1 row affected (0.37 sec)

Use the select statement to display records. The query is as follows −

mysql> select *from DateAndTimePartDemo;

The following is the output showing the current date and time −

+---------------------+
| YourDateandtime     |
+---------------------+
| 2018-11-24 13:40:36 |
+---------------------+
1 row in set (0.00 sec)

Let us now use the DATE_FORMAT() function to split the above date and time.

The query is as follows −

mysql> SELECT DATE_FORMAT(YourDateandtime, '%Y-%m-%d') OnlyYourDate,
-> DATE_FORMAT(YourDateandtime,'%H:%i:%s') OnlyYourTime from DateAndTimePartDemo;

The following is the output −

+--------------+--------------+
| OnlyYourDate | OnlyYourTime |
+--------------+--------------+
| 2018-11-24   | 13:40:36     |
+--------------+--------------+
1 row in set (0.03 sec)

The above is the detailed content of How to separate date and time in DATETIME in MySQL?. For more information, please follow other related articles on the PHP Chinese website!