date - MySQL: How to select records for this week?

Question

I have table temp with structure on sqlfiddle:

id(int 11 primary key)
name(varchar 100)
name2(varchar 100)
date(datetime)

I would like get record on this week, example if now 21.11.2013 i would like all rows on 18.11.2013 to 24.11.2013(on week)

Now I see next algorithm:

1. obtain weekday
2. calculate how many days ago was Monday
3. calculate the date Monday
4. calculate future date Sunday
5. make a request on date

Tell me please, is exist a shorter algorithm (preferably in the query MySQL)?

ADD Question is: Why this query select record on date 17.11.2013(Sunday) - 23.11.2013(Saturday) and how get records on date 18.11.2013(Monday) - 24.11.2013(Sunday) ?

query:

select * from temp
where yearweek(`date`) = yearweek(curdate())

Thanks!

question from:https://stackoverflow.com/questions/20120693/mysql-how-to-select-records-for-this-week

Answer 1

Use YEARWEEK():

SELECT *
FROM   your_table
WHERE  YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1)