Demo of getting records between two datesWe will use our student table and link this table to fee payment table to test various quires to collect records between two date ranges.
Here are our two tables with some sample data.
fee_id | id | dt | amount |
1 | 1 | 2013-01-08 | 200 |
2 | 1 | 2013-01-10 | 100 |
3 | 2 | 2013-01-24 | 120 |
4 | 3 | 2013-02-12 | 211 |
5 | 2 | 2013-02-07 | 150 |
6 | 3 | 2013-02-06 | 135 |
7 | 4 | 2013-02-14 | 100 |
|
id | name | class | mark |
1 | John Deo | Four | 75 |
2 | Max Ruin | Three | 85 |
3 | Arnold | Three | 55 |
4 | Krish Star | Four | 60 |
5 | John Mike | Four | 60 |
6 | Alex John | Four | 55 |
|
Here is our query to display records between two date ranges
SELECT * FROM `student_fee` WHERE dt BETWEEN '2013-01-09' and '2013-01-30'
We will get output displaying two records
fee_id | id | dt | amount |
2 | 1 | 2013-01-10 | 100 |
3 | 2 | 2013-01-24 | 120 |
Now Linking two tables we will display the records
SELECT * FROM `student_fee`,student WHERE student_fee.id=student.id and dt BETWEEN '2013-01-09' and '2013-01-30'
The output is here
fee_id | id | dt | amount | id | name | class | mark |
2 | 1 | 2013-01-10 | 100 | 1 | John Deo | Four | 75 |
3 | 2 | 2013-01-24 | 120 | 2 | Max Ruin | Three | 85 |
Try to find out total fee collected in Jan 2013
Try to find out how much John Deo had paid in Feb 2013
Download sql dump of student table
Download sql dump of studnet_fee table
This article is written by plus2net.com team.
plus2net.com
▼ More on Date & Time functions in SQL
|