Syntax error MySQL Query to convert from datetime to date?

MySQL Query to convert from datetime to date?

You can use CAST() function from MySQL to achieve this. The syntax is as follows −

SELECT CAST(yourColumnName as Date) as anyVariableName from yourTableName;

To understand the above syntax, let us first create a table. The query to create a table is as follows −

mysql> create table ConvertDateTimeToDate
-> (
-> ArrivalDatetime datetime
-> );
Query OK, 0 rows affected (0.37 sec)

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

mysql> insert into ConvertDateTimeToDate values(date_add(now(),interval -1 year));
Query OK, 1 row affected (0.19 sec)

mysql> insert into ConvertDateTimeToDate values('2017-11-21 13:10:20');
Query OK, 1 row affected (0.14 sec)

mysql> insert into ConvertDateTimeToDate values('2016-05-24 21:11:24');
Query OK, 1 row affected (0.26 sec)

mysql> insert into ConvertDateTimeToDate values('2012-04-30 04:05:50');
Query OK, 1 row affected (0.13 sec)

Let us now display all records from the table using select command. The query is as follows.

mysql> select *from ConvertDateTimeToDate;

The following is the output.

+---------------------+
| ArrivalDatetime     |
+---------------------+
| 2017-12-27 10:05:21 |
| 2017-11-21 13:10:20 |
| 2016-05-24 21:11:24 |
| 2012-04-30 04:05:50 |
+---------------------+
4 rows in set (0.00 sec)

Here is the query to convert from datetime to date in MySQL.

mysql> select cast(ArrivalDatetime as Date) as Date from ConvertDateTimeToDate;

The following is the output.

+------------+
| Date       |
+------------+
| 2017-12-27 |
| 2017-11-21 |
| 2016-05-24 |
| 2012-04-30 |
+------------+
4 rows in set (0.00 sec)
Updated on: 2020-06-25T13:42:38+05:30

8K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements