CODEWITHSUNDEEP
mysqlmysql-workbench
Smita Ahinave
Smita Ahinave

Reputation: 1888

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?

Upvotes: 9

Views: 3083

Answers (3)

Steve Chambers
Steve Chambers

Reputation: 39394

General Function

Here is a function that answers the more general question of "What is the value of n where my supplied date is the nth weekday occurrence of the month":

CREATE FUNCTION getNWhereDateIsNthWeekdayInMonth(`date` DATE)
RETURNS INT DETERMINISTIC BEGIN
  DECLARE firstOfMonthDate DATE;
  DECLARE dateDayOfMonth, firstOfMonthWeekday, dateWeekday, n INT;
  SET dateDayOfMonth = DAYOFMONTH(`date`); -- 1-31
  SET firstOfMonthDate = LAST_DAY(`date` - INTERVAL 1 MONTH) + INTERVAL 1 DAY;
  SET firstOfMonthWeekday = DAYOFWEEK(firstOfMonthDate); -- 1=Sun,...,7=Sat
  SET dateWeekday = DAYOFWEEK(`date`);                   -- 1=Sun,...,7=Sat
  SET n = ABS(
              dateDayOfMonth -- min=1,max=31
              - ((dateWeekday - firstOfMonthWeekday) % 7 -- min=-6,max=6
                 + 7) % 7 -- min=0,max=6
              - 1 -- min=-6,max=30
          )     -- min=0,max=30
          DIV 7 -- min=0,max=4
          + 1;  -- min=1,max=5*/
  RETURN n;
END//

Specific Usage

To answer your question, the function can be used (e.g. within a SELECT) as follows:

-- The following expression evaluates to TRUE if the supplied date is the
-- 2nd or 4th occurrence of the weekday in the month, or FALSE otherwise.
getNWhereDateIsNthWeekdayInMonth('2016-03-13') IN (2, 4)

Demo

Here is a demo showing the function in use for various values: SQL Fiddle demo

Upvotes: 3

Rick James
Rick James

Reputation: 142218

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.

Upvotes: 7

Dylan Su
Dylan Su

Reputation: 6065

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.

Upvotes: 6

Related Questions