How to calculate a third monday of every month using SQL statement?

Question

i need to trigger a notification. this notification has to be triggered every third monday of every month.

Answer 1

SELECT 
(
  DAYOFWEEK(NOW()) = 2  
  AND 
  DAYOFMONTH(NOW()) BETWEEN 15 AND 21
) 
AS send_notice_today;

Answer 2

So perhaps MORE GENERALLY if you can get the "week of month" for the date using this:

(FLOOR((DAYOFMONTH(given) - 1) / 7)) AS 'week_of_month'

which I believe provides an accurate 0 based week-of-month index for a given date. then you can use the value to find any nth as in:

WHERE (week_of_month) = n AND weekday = {weekday}

you can also use the above to get the "last {weekday}" by:

WHERE (week_of_month >= 4) and weekday = {weekday}

note that the week_of_month can range from 0 to 5 (e.g., a 31 day month whose 1st falls on Saturday will have the 31st in the 6th week (5 as a 0 based index)

hope this helps ...

OK a bit more ... you might define the above as a function as in:

CREATE FUNCTION WEEKOFMONTH(given date) RETURNS int DETERMINISTIC RETURN (FLOOR((DAYOFMONTH(given) - 1) / 7))

and add another function:

CREATE FUNCTION WEEKNAME(given date) RETURNS text CHARACTER SET utf8 COLLATE utf8_unicode_ci DETERMINISTIC RETURN (CONCAT(ELT(WEEKOFMONTH(given)+1,'1st ','2nd ','3rd ','4th/Last ','5th/Last '),DAYNAME(given)))

then you can simply say things like

SELECT * FROM dataTable WHERE WEEKNAME(your_date_field) = "3rd Wednesday"

... I struggled with how the 4th/5th should be returned from WEEKDAY and settled on adding "/Last" for both under the theory that this is "good enough" should one want to test for either 4th, 5th or Last. Using this you can do:

SELECT * FROM dataTable WHERE WEEKNAME(your_date_field) LIKE "%Last%"

Answer 3

Try using dayofweek and dayofmonth functions. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek

Somehow you can check how many weeks are there from 1st of month to curdate() with dayofmonth (using an operation mod 7), and dayofweek should be 5 (thursday)

Answer 4

The approach that I'm taking is get the 1st Monday of the month, and depending on when in the month it is, add either 2 or 3 weeks to it (since when it falls out before/on Monday, you only need to walk 2 more weeks):

 ;with  
 filler as (select row_number() over (order by a) a from (select top 100 1 as a from syscolumns) a cross join (select top 100 1 as b from syscolumns) b),
 dates as (select dateadd(month, a-1, '1/1/1900') date from filler where a <= 2000),
 FirstMonday as (
 select dateadd(day, case   datepart(weekday,Date)
                            when 1 then 1
                            when 2 then 0
                            when 3 then 6
                            when 4 then 5
                            when 5 then 4
                            when 6 then 3
                            when 7 then 2
                        end, Date) as Date
        ,case when datepart(weekday,Date) = 1 then 3 else 2 end as Weeks
 from   dates
 )
 select dateadd(week, Weeks, Date) as ThirdMonday
 from FirstMonday

Answer 5

This one calculates the first Monday of any month given the year-month-day

SET @firstday = '2015-04-01';
SELECT ADDDATE( @firstday , MOD((9-DAYOFWEEK(@firstday)),7)) as first_monday;

This one calculates the third Monday of any month given the year-month-day

SET @firstday = '2015-01-01';
SELECT ADDDATE( @firstday , MOD((23-DAYOFWEEK(@firstday)),21)) as third_monday;

This one calculates the third Friday of any month given the year-month-day

SET @firstday = '2015-09-01';
SELECT ADDDATE( @firstday , MOD((20-DAYOFWEEK(@firstday)),20)) as third_friday;

Thanks to @Brewal for the original question and @User2208436 for pointing us toward the answer.

Answer 6

Here's an answer that does not use DAYOFWEEK or DAYOFMONTH. It uses DATEADD and DATEPART only.

We'll need two helper functions:

CREATE FUNCTION dbo.day_of_week(@date Date)
RETURNS INT
AS BEGIN
    -- (1 for Sunday, 2 for Monday, etc)
    RETURN DATEPART(dw, DATEADD(year, year(@date)-1900, DATEADD(month, month(@date)-1, DATEADD(day, day(@date)-1, 0))))
END
GO

CREATE FUNCTION dbo.date_from_parts(@year INT, @month INT, @day INT)
RETURNS DATE
AS BEGIN
    RETURN DATEADD(year, @year-1900, DATEADD(month, @month-1, DATEADD(day, @day-1, 0)))
END
GO

Then using the following example data:

DECLARE @day_of_week INT
SET @day_of_week = 2 -- Monday

DECLARE @year INT
DECLARE @month INT
SET @year = 2016
SET @month = 11

Let's first obtain the FIRST Monday of the month:

We will add an offset, (day_of_week - day of week of first day of the month) % 7, to the first day of the month.

(Also notice we need the construction ((x % n) + n) % n instead of just x % 7, to keep the answer within 0 and 6. For example, just SELECT -3 % 7 returns -3! See Mod negative numbers in SQL just like excel)

Now here's the final construction to obtain the first Monday of the month:

SELECT
    DATEADD(
        dd,
        (((@day_of_week -
           dbo.day_of_week(dbo.date_from_parts(@year, @month, 1))) % 7) + 7) % 7,
        dbo.date_from_parts(@year, @month, 1)
    )

To obtain the third Monday of the month, add 14 to the second term of this answer.

Answer 7

If @firstday is the first day of the month

select date_add(@firstday,
         interval (if(weekday(@firstday)>0,21-weekday(@firstday),14)) day);

yields the 3rd monday of the month.

Tested with all months of 2018.

Seems simpler than previous solutions.

The key is the if function on weekday.

if weekday = 0 (first day is a monday), add 14 days if weekday > 0, add 21 days and subtract the weekday

Answer 8

DECLARE @YEAR DATE='2019-01-01'
SELECT DATEADD( d, 23-(DATEPART(dw,@YEAR )%21),@YEAR )

This will help to get the third Monday of whatever month you want.

Answer 9

The date of the third monday of the current month would be given by the SQL statement:

SELECT date_add(date_sub(curdate(),INTERVAL dayofmonth(curdate())-1 DAY), 
    INTERVAL (7-weekday(date_sub(curdate(),INTERVAL dayofmonth(curdate())-1 DAY)))+14 DAY)