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 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
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.
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
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.
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.
Similarly, you can change the variable $dt1 to a new date format.
Now we can use inside our query.
SELECT * FROM`dt_tb`WHEREdtBETWEEN'$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.