To find mysql recodrs between specific date range based on start date and end date fields

While working on a report generation system i had to perform a query to check whether records exist in ‘reports’ table between a date range received from two form fields “start_date” and “end_date”. To make it more specific i will try to explain it with the help of an example.

I receive “01 Jan, 2008” and “01 Feb 2008” from the form data fields as “start_date” and “end_date” respectively. Now i want to make the following checks into my mysql table “reports”:

• Check whether records between (or with the exact) supplied date range “01 Jan, 2008” and “01 Feb 2008” exist.
• Check whether records which have a start date before “01 Jan, 2008” and have an end date between “01 Jan, 2008” and “01 Feb 2008” exist.
• Check whether records which have a end date between “01 Jan, 2008” and “01 Feb 2008”
• Check whether records which have a start date before “01 Jan, 2008” and an end date after “01 Feb 2008” exist.

In other words, i want to check/find all those records which intersect the given date range(01 Jan, 2008 and 01 Feb 2008) in any case.

After digging some hard i came out with a surprisingly short ‘fix’ below:

SELECT *
FROM `reports`
WHERE end_date >= ’01 Jan, 2008′
AND start_date <= ’01 Feb 2008′

It worked for me like a charm! I hope this works for you as well.

Post your valuable suggestions and comments.

Thanks.

Share on Facebook

Tweet

Follow us

Save