sss sss - 3 months ago 9
MySQL Question

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

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

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.