Syntax error How to query between two dates in MySQL?

How to query between two dates in MySQL?

You can query between dates with the help of BETWEEN statement. The syntax is as follows −

select *from yourTableName where yourColumnName between ‘yourStartingDate’ and curdate().

Use curdate() or now(), both these functions will work. To understand the above syntax, let us create a table −

mysql> create table BetweenDateDemo
   −> (
   −> StartDate datetime
   −> );
Query OK, 0 rows affected (0.78 sec)

Insert some records in the table with the help of the following query −

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

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

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

mysql> insert into BetweenDateDemo values(date_add(now(),interval 1 year));
Query OK, 1 row affected (0.12 sec)

mysql> insert into BetweenDateDemo values(date_add(now(),interval 2 year));
Query OK, 1 row affected (0.12 sec)

mysql> insert into BetweenDateDemo values(date_add(now(),interval 3 year));
Query OK, 1 row affected (0.16 sec)

Now you can display all records from the table with the help of select statement. The query is as follows −

mysql> select *from BetweenDateDemo;

The following is the output −

+---------------------+
| StartDate           |
+---------------------+
| 2017-12-08 11:45:47 |
| 2016-12-08 11:45:56 |
| 2015-12-08 11:46:01 |
| 2019-12-08 11:46:05 |
| 2020-12-08 11:46:11 |
| 2021-12-08 11:46:15 |
+---------------------+
6 rows in set (0.00 sec)

Now perform select between dates using the syntax discussed above −

mysql> select *from BetweenDateDemo where StartDate between '2014-8-12' and curdate();

The following is the output −

+---------------------+
| StartDate           |
+---------------------+
| 2017-12-08 11:45:47 |
| 2016-12-08 11:45:56 |
| 2015-12-08 11:46:01 |
+---------------------+
3 rows in set (0.00 sec)

Use ORDER BY to get in a sorted order. The query is as follows −

mysql> select *from BetweenDateDemo where StartDate between '2014-8-12' and curdate() order by StartDate;

The following is the output −

+---------------------+
| StartDate           |
+---------------------+
| 2015-12-08 11:46:01 |
| 2016-12-08 11:45:56 |
| 2017-12-08 11:45:47 |
+---------------------+
3 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

18K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements