MySQL CAST as DATE?

There is no difference between cast as Date and date() function in MySQL.

The syntax of both cast() and date() is as follows −

cast(yourDateTimeColumnName as Date)
date(yourDateTimeColumnName)

Both functions internally call Item_date_typecast. To check both the functions, let us create a table. The query to create a table is as follows −

mysql> create table CastAsDateDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> ArrivalTime datetime,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.71 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into CastAsDateDemo(ArrivalTime) values('2014-1-13 13 −45 −46');
Query OK, 1 row affected (0.16 sec)

mysql> insert into CastAsDateDemo(ArrivalTime) values('2016-4-11 10 −30 −31');
Query OK, 1 row affected (0.48 sec)

mysql> insert into CastAsDateDemo(ArrivalTime) values('2019-1-9 19 −17 −49');
Query OK, 1 row affected (0.13 sec)

mysql> insert into CastAsDateDemo(ArrivalTime) values(date_add(now(),interval 2 day));
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from CastAsDateDemo;

The following is the output −

+----+----------------------+
| Id | ArrivalTime          |
+----+----------------------+
| 1  |2014-01-13 13 −45 −46 |
| 2  |2016-04-11 10 −30 −31 |
| 3  |2019-01-09 19 −17 −49 |
| 4  |2019-01-11 19 −17 −59 |
+----+---------------------+
4 rows in set (0.00 sec)

Here is the query to cast as a date using cast() −

mysql> select cast(ArrivalTime as Date) as only_Date from CastAsDateDemo;

The following is the output −

+------------+
| only_Date  |
+------------+
| 2014-01-13 |
| 2016-04-11 |
| 2019-01-09 |
| 2019-01-11 |
+------------+
4 rows in set (0.00 sec)

Here is the query to cast as a date using date(). The query is as follows −

mysql> select date(ArrivalTime) as only_Date from CastAsDateDemo;

The following is the output −

+------------+
| only_Date  |
+------------+
| 2014-01-13 |
| 2016-04-11 |
| 2019-01-09 |
| 2019-01-11 |
+------------+
4 rows in set (0.00 sec)