Canman2005 Posted May 13, 2007 Share Posted May 13, 2007 Hi I have a further question to my last post, but its slightly different. I have a table called "events" with a "start_date" and "end_date", the dates are stored as YYYY-MM-DD an example table would be ID START_DATE END_DATE 1 2007-03-23 2007-03-28 2 2007-04-02 2007-04-02 I want to run a query on this table and return all rows that have todays date and the next 6 days (so 1 week) within the START_DATE and END_DATE fields. So if todays date was 2007-03-26, then it would return only row 1 from the above example table, and if todays date was the 2007-04-01 then it would return just row 2 as it has a START_DATE starting the next day. Does that make sense? I have tried to do this query with the BETWEEN function, but it doesnt seem to be returning the correct result and sometimes no result. Can anyone help me? Thanks in advance Dave Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/ Share on other sites More sharing options...
AndyB Posted May 13, 2007 Share Posted May 13, 2007 And is the database field type DATE for date (good) or did you use a text field (bad)? Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252306 Share on other sites More sharing options...
Canman2005 Posted May 13, 2007 Author Share Posted May 13, 2007 Yep, the type is set to DATE in the format 0000-00-00 Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252321 Share on other sites More sharing options...
Canman2005 Posted May 13, 2007 Author Share Posted May 13, 2007 Sorry, type is set to DATE for both START_DATE and END_DATE, both set to 0000-00-00 Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252326 Share on other sites More sharing options...
marf Posted May 13, 2007 Share Posted May 13, 2007 Do you want to obtain the results with a Single MySQL query? Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252338 Share on other sites More sharing options...
Canman2005 Posted May 13, 2007 Author Share Posted May 13, 2007 If possible, if not, whatever way is best Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252341 Share on other sites More sharing options...
Canman2005 Posted May 13, 2007 Author Share Posted May 13, 2007 Anyone? Sorry its driving me crazzy. Still trying though Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252364 Share on other sites More sharing options...
Barand Posted May 13, 2007 Share Posted May 13, 2007 SELECT * FROM events WHERE startdate BETWEEN CURDATE() AND CURDATE() + INTERVAL 6 DAY OR enddate BETWEEN CURDATE() AND CURDATE() + INTERVAL 6 DAY Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252423 Share on other sites More sharing options...
Canman2005 Posted May 13, 2007 Author Share Posted May 13, 2007 Thanks, saved my life Link to comment https://forums.phpfreaks.com/topic/51226-solved-query-help/#findComment-252431 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.