Canman2005 Posted August 23, 2009 Share Posted August 23, 2009 Hi all I have a database table that has a field called `date`, the field is in the format YYYY-MM-DD How could I write a query which would select all rows happening this week, so if today was a Tues, it would grab Tues, Wed, Thurs, Fri, Sat, Sun and if it was a Fri it would grab Fri, Sat, Sun Thanks very much everyone Dave Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 23, 2009 Share Posted August 23, 2009 Untested but should work - WHERE WEEK(CURDATE(),1) = WEEK(date,1) AND date >= CURDATE() Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 23, 2009 Author Share Posted August 23, 2009 Thanks Tried that, but returned nothing I have a row which has a date of 23/08/2009 which is todays date, but the query didnt seem to pick it up Any ideas anyone? thanks again Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 23, 2009 Share Posted August 23, 2009 23/08/2009 is not the proper DATE data type format YYYY-MM-DD. The WHERE clause I posted works, I just tested it - $query = "SELECT * FROM table_name WHERE WEEK(CURDATE(),1) = WEEK(date,1) AND date >= CURDATE()"; That returned rows with today's date 2009-08-23 (Sunday of the current week) and it did not return rows with yesterday's or tomorrow's date. Temporarily setting the current date to yesterday, it returns rows that match yesterday and today as expected. Temporarily setting the current date to tomorrow (Monday of next week), it returns row that match Monday - Sunday of next week. The only problem it has is that if you have a date with a future year in the same week as the current week, it will return those as well. You would need to add a test for YEAR(CURDATE()) = YEAR(date) to eliminate future years. Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 23, 2009 Author Share Posted August 23, 2009 thanks Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 23, 2009 Author Share Posted August 23, 2009 I guess just adding AND date LIKE '%$year%' to the end of the query and year being defined as $year = date('Y'); will be good enough Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 23, 2009 Share Posted August 23, 2009 Why write some slow parsed/tokenized/interpreted php code when you can just add the following to the query - AND YEAR(CURDATE()) = YEAR(date) Anything you can manage to do directly in a query will be a minimum of 10 times faster than if you do it using php code. Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 23, 2009 Author Share Posted August 23, 2009 thanks man 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.