| | |
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.
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.
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.
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');
| | 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 |
|
|
|
|
|
|
If you are facing any problem in viewing this page, please tell us
