Fetching records between two date ranges

We can collect records between two date fields of a table by using BETWEEN query. We can use this to get records between two years or between two month . We can combine all this and try for getting records between two date ranges.

Between two years

We will first start with displaying records between two years. Before that you can read on how to get year part from a date field. Now let us try to get records between year 2004 and 2005. Here is our query

SELECT * FROM `dt_tb` WHERE year( dt2 ) between 2004 and 2005

IN the above query dt2 is our date field and in the result both the years 2004 and 2005 will be included in our records.

Between two month ranges.

Related Tutorial
• Date Difference • Last 15 days record • Records of last 10 minutes • Between a range of Records

Now let us collect the records between two months. Note that if we are using only month in our between command then for any year the range of month we specified will be returned by the query. For example if we ask for records between Feb and Aug months then we will get records of between the month Feb and Aug for all the years. Here is the example.

SELECT * FROM `dt_tb` WHERE month(dt) between '02' and '08'

The above query will return us records of all the months between February and August of any year. We can specify the year also along with the months like this

SELECT * FROM `dt_tb` WHERE month(dt) between '02' and '08' and year(dt) between 2004 and 2005

There are more details on how to get the month part of any date field here.

Between two date ranges

Now let us move to select a range of records between two dates. Here is the sql for this

SELECT * FROM `dt_tb` WHERE dt BETWEEN '2005-01-01' AND '2005-12-31'

Must Read How to get records between two ranges using between and DATE_SUB function.

Read demo of how date is used to collect records

Here is the code for SQl dump of the file to create your table for testing.

CREATE TABLE dt_tb (
id int(2) NOT NULL auto_increment,
dt datetime NOT NULL default '0000-00-00 00:00:00',
dt2 date NOT NULL default '0000-00-00',
PRIMARY KEY (id)
) TYPE=MyISAM;

# Dumping data for table `dt_tb`

INSERT INTO dt_tb VALUES (1, '2004-10-26 00:00:00', '2005-01-25');
INSERT INTO dt_tb VALUES (2, '2004-05-05 23:56:25', '2005-06-12');
INSERT INTO dt_tb VALUES (3, '2005-12-08 13:20:10', '2005-06-06');
INSERT INTO dt_tb VALUES (4, '2003-05-26 00:00:00', '2007-12-18');
INSERT INTO dt_tb VALUES (5, '2007-12-18 00:00:00', '2003-08-16');

Date & Time functions used in Query for MySQL Table

Getting formatted date value from date field in MySQL

date_add function to calculate date & time of MySQL table

Formatting date and time before adding to date field of MySQL

String data to Date & time Format by using str_to_date

Formatting string data stored in varchar field to date value

Automatically updating / inserting current date and time value in a DATETIME field

Records of each day by using group by command

Collecting records between two date ranges from MySQL table fields

Getting date values from MYSQL table in readable format including time

Difference in days between two date fields

Getting the year part from date field

Getting the month part from date field

Getting the day part from date field

Records of today or records of last X seconds by using curdate() or unix_timestamp

Records of last one month from today by using date field

Records of present week days by using dayofweek function

rose	09-06-2009
I need to retrieve the records which are between the 2 days(TWO dates are of two different fields)
rei	13-07-2009
I also need to get the records between dates! Is it any solution?
Naveen Ram	24-09-2009
Hi, i want to select all the records which has date below 9/24/09. for eg: in datebase the records are Date 9/30/2009 9/24/2009 10/3/2009 9/26/2009 9/25/2009 in that i want to select only 9/24/09 and below. i write the query as Select Date From DateTable Where Date <= '9/24/2009'. i got result as Date 9/24/2009 10/3/2009
Girihdar	02-10-2009
hi i need the query for display the dates between the two dates, and my condition is only between dates, no retrieving the data from the database.
Praise	05-10-2009
Is there a way to write this SQL code better. cos ikeep getting an error: SELECT qryViewExcesses.CIID, qryViewExcesses.CustomerName, qryViewExcesses.Industry, qryViewExcesses.RelationshipManager, qryViewExcesses.FirstDate, qryViewExcesses.DaysInExcess, qryViewExcesses.EndDate, qryViewExcesses.CCY, tbl_AllExcesses.[Date Of Report], tbl_AllExcesses.[EXCESS AMOUNT] FROM tbl_AllExcesses RIGHT JOIN qryViewExcesses ON tbl_AllExcesses.[Customer ID] = qryViewExcesses.CIID WHERE (((tbl_AllExcesses.[Date Of Report]) Between [qryViewExcesses].[FirstDate] And [qryViewExcesses].[EndDate]));
Karthik	09-10-2009
hi i need the query for display the dates between the two dates, and my condition is only between dates, no retrieving the data from the database. SELECT * FROM Timesheet WHERE date BETWEEN '25/09/2009' and '07/10/2009' i didnt get any result regarding this.. pls help me to solve this problem
Natalie	03-11-2009
I need to find results of a sale date that are between sysdate and 4 days from sysdate. How can I do this?
smo	04-11-2009
You have to use CURDATE function, see the Must Read section at the top or visit this CURDATE
Anita	05-11-2009
Hi, I want to display all the records between '2009-11-02' to '2009-11-05'. Using Between clause display dates from 2009-11-02 2009-11-03 2009-11-04 but not 2009-11-05. How can i get 2009-11-05 as well. thank you.
saintjab	24-01-2010
I have two culumns date and amount. I want to find the sum between two given dates assuming table name is money.
kuthey	13-02-2010
@saintjab: Dats easy. use group by
vincent	24-03-2010
"Select Date From DateTable Where Date <= '9/24/2009'" use bettween..
Pattanayak	30-03-2010
Hi Anita, Please use the follwoing statement: SELECT * FROM Tablename WHERE dt BETWEEN '2009-11-02' AND '2009-11-06' thanks
ankita bansal	31-03-2010
create proc SP_Get_Result ( @DateFrom nvarchar(50), @DateTo nvarchar(50) ) as SELECT * FROM Timesheet WHERE date BETWEEN @DateFrom and @DateTo i didnt get any result regarding this.. pls help me to solve this problem
Big Vern	16-04-2010
I am looking to use the between dates action in Mysql, but want to use variables, so instead of SELECT * FROM Tablename WHERE date BETWEEN '2009-11-02' AND '2009-11-06' USing PHP I want to use $datefrom = date('Y-m-d',strtotime(date1)); $dateto = date('Y-m-d',strtotime(date2)); to adapt the query to SELECT * FROM Tablename WHERE date BETWEEN ".$datefrom." AND ".$dateto,"" I have tried this but it doesnt work, any ideas?
Manaat	26-04-2010
i have a table in oracle where one column is xmltype and one node in the xml is date i have to write a query which selects the data from this table but between a specific date range please help
kishori	11-07-2010
I have a table in mysql where one column is date and another column is medical status( FIT/Repeat ). How can I fetch the records by date and medical status. either FIT or Repeat
dharma	09-08-2010
hi, i have to select the first sunday,first tuesday from date 17-8-2010 to 17-9-2010
Rik	25-08-2010
hey i am trying to use the: SELECT * FROM Tablename WHERE date BETWEEN ".$datefrom." AND ".$dateto,"" too is there any ideas to dong this ive been trying for about 2 - 3 weeks
kamlesh makvana	07-09-2010
i want to display a grap of data between two date using between clause plz help me
Mahesh	14-12-2010
This is Very Perfect Answer for this question
Dan Lubbs	07-11-2012
... WHERE date BETWEEN [FromDate] and [ThruDate]. Example of a FromDate is 9/1/2012 and ThruDate is 10/30/2012. I want to prompt with a normal date style not 2012-09-01.
Venbha	08-01-2013
I got my need. Thank You

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email	( not to be displayed)Privacy Policy
1+2=	This is to prevent automatic submission by spammers. Please enter the result of the sum as asked