How to find the first Sunday of the month in MySQL?

Question

I did not find any of examples for MySQL - all of them were quite complicated.

How can I SELECT the first Sunday of the month?

Answer 1

So choose the first day of the month: 2012-01-01 (or whatever month and year you want).

Get the weekday index of the date. Indexes here are from 0 to 6.

Subtract that index from 6 and you will get how many days you need to add until the date is Sunday.

Add that amount of days to the chosen day.

SELECT DATE_ADD("2012-01-01 10:00:00", INTERVAL (6 - WEEKDAY("2012-01-01 10:00:00")) DAY);

Or:

SELECT DATE_ADD("2012-01-01", INTERVAL (6 - WEEKDAY("2012-01-01")) DAY);