0

I have a table which look like this:

  Date       |    Day     |    Ingredients
-------------------------------------------
2014-08-20   | Wednesday  | Salt
2014-08-21   | Thursday   | Sugar
2014-08-22   | Friday     | Salt&Sugar
2014-08-28   | Thursday   | Salt

And I want to have only the dates there are in one week. Should I make an extra column 'Week' with the number of the week or is there a solution where I can set the beginning and the end of a week to only have the actual week. So I have all rows from the actual week.

SELECT * FROM table WHERE *`Date is in the actual week`*
user3493797
  • 63
  • 7
  • How do you specify which week? – shree.pat18 Aug 22 '14 at 09:18
  • why don't you use 7 queries?(1 per day) – nafas Aug 22 '14 at 09:18
  • A week goes from Monday to Sunday for me, and even it is Sunday I need the data from the past 6 days. And when its Monday after that I need the data from Monday and the next 6 days. – user3493797 Aug 22 '14 at 09:19
  • This might help http://stackoverflow.com/questions/1267126/how-do-you-get-the-week-start-date-and-week-end-date-from-week-number-in-sql http://stackoverflow.com/questions/10007861/getting-the-starting-and-ending-date-of-week-in-sql-server – Shivang MIttal Aug 22 '14 at 09:23

1 Answers1

2

You mean to get the data of current week?

Use the YEARWEEK function.

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

Update: Use mode 1 if the beginning of week is Monday.

xdazz
  • 158,678
  • 38
  • 247
  • 274
  • You'll most likely want to add [mode 1](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week) to YEARWEEK. – Joachim Isaksson Aug 22 '14 at 09:25