CODEWITHSUNDEEP
mysqldate
Aitvaras
Aitvaras

Reputation: 261

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

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?

Upvotes: 4

Views: 4297

Answers (2)

Alexander Kolinko
Alexander Kolinko

Reputation: 31

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

SELECT ADDDATE( '2021-08-01' , MOD((8-DAYOFWEEK('2021-08-01')),7))

Upvotes: 0

Aitvaras
Aitvaras

Reputation: 261

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);

Upvotes: 8

Related Questions