Demo of getting records between two dates

We 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

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

Generating query based on user inputs of date from two calendars

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

saraazee	03-06-2014
how can we retrieve the data from database by a particular month?

Queries INSERT UPDATE

Aggregate Queries AVG ( Average ) SUM MIN MAX

✖

We use cookies to improve your browsing experience. . Learn more

FORUM . Contact us

©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer