Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
295 Views
Advertisements