How to get this coming Sunday's date?

Question

I need to get "this coming Sunday"'s date

How could I do this?

I've seen the DAYOFWEEK function but that's not really what I'm after (might be useful in the WHERE clause)

EDIT:

I changed question back from my change to Saturday to the original Sunday since I got some valid answers for Sunday and it may help some people in the future

I found a number of other 'end of week' date questions and answers elsewhere (including here on SO)

The most useful solution for me was the accepted answer found here.

Answer 1

UPDATE

Better use curdate() instead of now(), so the final conversion to DATE type via date() can be omitted.

SELECT curdate() + INTERVAL 6 - weekday(curdate()) DAY;

for next sunday or

SET @date = '2014-03-05';

SELECT @date + INTERVAL 6 - weekday(@date) DAY;

for a variable date.

---

ORIGINAL ANSWER

Simply use this statement for the next sunday:

SELECT date(now() + INTERVAL 6 - weekday(now()) DAY);

Output

+-----------------------------------------------+
| date(now() + INTERVAL 6 - weekday(now()) DAY) |
+-----------------------------------------------+
| 2014-04-13                                    |
+-----------------------------------------------+

Explanation:

weekday(now()) returns the current weekday (starting with 0 for monday, 6 is sunday). Subtract the current weekday from 6 and get the remaining days until next sunday as a result. Then add them to the current date and get next sunday's date.

Or if you want to keep it flexible to work with any date:

SET @date = '2014-03-05';

SELECT date(@date + INTERVAL 6 - weekday(@date) DAY);

Output

+-----------------------------------------------+
| date(@date + INTERVAL 6 - weekday(@date) DAY) |
+-----------------------------------------------+
| 2014-03-09                                    |
+-----------------------------------------------+