[SOLVED] Query rows this week

[Canman2005]

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

[PFMaBiSmAd]

Untested but should work -

 

WHERE WEEK(CURDATE(),1) = WEEK(date,1) AND date >= CURDATE()

[Canman2005]

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

[PFMaBiSmAd]

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.

[Canman2005]

thanks

[Canman2005]

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

[PFMaBiSmAd]

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.

[Canman2005]

thanks man