How to check whether a date is the 2nd or 4th Sunday of the month in MySQL?

Question

Can I check whether a supplied date is the second or fourth Sunday of the month?

E.g: Suppose I entered 13/3/2016 then its 2nd Sunday in month of March. So how can I come to know it using MySQL?

Answer 1

Given that x is a DATE or DATETIME:

DAYNAME(x) = 'Sunday' AND
FLOOR((DAYOFMONTH(x) + 6 ) / 7) IN (2, 4)

2,4 -- for 2nd and 4th Sunday.

FLOOR... -- gives which week of month.

So, this code can be easily adapted to a different day of week and different weeks of month.

Answer 2

Here is a solution:

SET @var = date '2016-03-13';
select 
    sum( month(@var - INTERVAL 7 * t.a DAY) = month(@var) ) as WeekOfMonth, 
    dayname(@var) as Weekday
from (select 0 as a union select 1 union select 2 union select 3 union select 4) as t;

Output:

mysql> SET @var = date '2016-03-13';
Query OK, 0 rows affected (0.00 sec)

mysql> select
    -> sum( month(@var - INTERVAL 7 * t.a DAY) = month(@var) ) as WeekOfMonth,
    -> dayname(@var) as Weekday
    -> from (select 0 as a union select 1 union select 2 union select 3 union select 4) as t;
+-------------+---------+
| WeekOfMonth | Weekday |
+-------------+---------+
|           2 | Sunday  |
+-------------+---------+
1 row in set (0.00 sec)

To use in your environment, just replace '@var' with you date input.