Syntax error How to part DATE and TIME from DATETIME in MySQL?

How to part DATE and TIME from DATETIME in MySQL?

To part DATE and TIME from DATETIME, you can use the DATE_FORMAT() method from MySQL. The syntax is as follows −

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

To understand the above method DATE_FORMAT(), let us create a table with data type “datetime”.

Creating a table −

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

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

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

Display the record with the help of select statement. The query is as follows −

mysql> select *from DateAndTimePartDemo;

The following is the output that displays current date and time −

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

Let us now part the above datetime with the help of DATE_FORMAT() function.

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)
Updated on: 2019-07-30T22:30:24+05:30

599 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements