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.

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

  1. SELECT * FROM ADMIN_AUDIT_LOG WHERE INSRT_DATE >= ‘2011-06-21’ AND INSRT_DATE = ‘2011-06-21’ AND INSRT_DATE <= '2011-06-23';

    I change the next date,that time its give 2011-06-21 to 2011-06-22 value.

    Please can you advice me.

    Thanks
    Venkat

Leave a Reply

Your email address will not be published. Required fields are marked *