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 months. We can combine all this and try for getting records between two date ranges.
Between Dates Query

Between two years

We will first start by 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 the years 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.
Records of different date ranges by using DATE_SUB(), CURDATE() and BETWEEN() query.

Between two month ranges.

Now let us collect the records between two months ( Say Feb to Aug months without any year specification). Note that if we are using the only a month in our between command so 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
The above query may not give us the desired result as it will collect records of Feb to Aug for the years 2004 and 2005. To get the records between two months with the year we have to change this query. Let us say we want records from March 2015 to Feb 2016.
SELECT *  FROM table_name  
WHERE dte_field  BETWEEN '2015-03-01'  AND LAST_DAY('2016-02-01')

By using LAST_DAY function we are getting the last date of the month.

Generate Query by using Month & Year from Calendar

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'

Date Format to use in query

You have seen we have used 'Y-m-d' date format in our query. We may not get data always in this format so here is the PHP code to convert to this format before using inside a query.
$date = new DateTime($dt2);  
$dt2 = $date->format('Y-m-d');
Similarly, you can change the variable $dt1 to a new date format.
Now we can use inside our query.
SELECT *  FROM `dt_tb`  WHERE dt  BETWEEN '$dt1'  AND '$dt2'
When we are offering input fields to the user to enter date then it is better to offer two calendars to the user to select dates. Here the user may like to enter one date as FROM date and expect to get all records starting from that date. The same way user may enter only TO date and expect all records equal to or before the TO date. Using these conditions we can generate Query.

Generate Query by using dates from Calendar

Generate Query by using date and time
Once the query is generated by using the above example, we can apply it to any table and generate the records.

DEMO of how date is used to collect records

Here is the code for the 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)  
);  

# Dumping data for table `dt_tb`  

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

Questions


SQL Date References Records between two dates using DATE_SUB



Subscribe to our YouTube Channel here



plus2net.com
We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles Contact us
©2000-2025   plus2net.com   All rights reserved worldwide Privacy Policy Disclaimer