[SOLVED] date range selection issue in mysql (when compared in two columns of the table)

[seenu_vas80]

Hi all,

 

I am facing an issue with MYSQL query.

 

I am having a table named “report_hours”

 

Id Category_type hours start_date end_date

1 2 2 2009-05-02 2009-05-03

2 4 23 2009-05-02 2009-05-07

3 6 12 2009-04-02 2009-05-07

 

From the above table I am generating a report, where the user will be prompted for “From Date” and “To Date”

 

Now I would like to retrieve the records which are between the dates given by the user

 

I have tried some options with keyword “between clause” in mysql but I didn’t get the exact results, when the user has selected From date as “2009-04-01” and To date as “2009-05-08”

 

I have listed down the query which I have tried below:

 

Query no 1:

SELECT * FROM ` report_hours ` WHERE (`start_date` BETWEEN '2009-04-01' AND '2009-05-08')

OR (`end_date` BETWEEN '2009-04-01' AND '2009-05-08')

 

Query no 2:

SELECT * FROM ` report_hours ` WHERE (DATE('2009-04-01') BETWEEN DATE(start_date) AND DATE(end_date) )OR (DATE('2009-05-08') BETWEEN DATE(start_date) AND DATE(end_date)

))

 

 

I need all the 3 records to be displayed when the above date has been selected. Is there any option available in mysql itself to search for date in a range (between two columns?). Need help on this.

 

Regards

Srinivasan

 

[kickstart]

Hi

 

Not sure why either wouldn't work.

 

What results are you getting with them?

 

All the best

 

Keith

[vintox]

maybe this query would work:

 

SELECT * FROM `report_hours` 
    WHERE
        (start_date>'2009-04-01' && start_date<'2009-05-08')
        OR
        (end_date>'2009-04-01' && end_date<'2009-05-08')

[seenu_vas80]

Both of you thanks for the reply.

 

finally, I found the solution

 

SELECT *

FROM `report_hours`

WHERE (

(

start_date >= '2009-04-01' && start_date <= '2009-05-08'

)

OR (

end_date >= '2009-04-01' && end_date <= '2009-05-08'

)

OR (

'2009-04-01' BETWEEN start_date AND end_date

 

OR '2009-05-08' BETWEEN start_date AND end_date

)

)

 

the query which "vintox" sent has not worked for few combinations, but it gave us the idea to find out the solution. Still the testing is going on.

 

Thanks a lot.

 

Regards,

Srinivasan.C