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 Quote Link to comment 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)? Quote Link to comment 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 Quote Link to comment 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 Quote Link to comment 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? Quote Link to comment 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 Quote Link to comment 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 Quote Link to comment 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 Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted May 13, 2007 Author Share Posted May 13, 2007 Thanks, saved my life Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.