How to calculate the third Monday of each month using an SQL query?

Question

I need to call a notification. This notification should run every third month of every month.

Answer 1

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

Answer 2

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

Somehow you can check how many weeks are left from the 1st month to execute curdate () using dayofmonth (using operating mode 7), and dayofweek should be 5 (Thursday)

Answer 3

The date of the third Monday of the current month will be provided 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) 

Answer 4

The approach I take is getting the first Monday of the month, and depending on when it will be this month, add 2 or 3 weeks to it (since when it drops out before / on Monday, you only need to go 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 calculator 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 calculates the third Monday of any month, given year-month-day

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

This 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 to the answer.

Answer 6

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

We 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 me first get the FIRST Monday of the month:

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

(Note that we need the ((x % n) + n) % n construct instead of just x % 7 to keep the answer at <T29> and 6 For example, only SELECT -3 % 7 returns -3 ! See Change negative numbers in SQL is the same as Excel )

Now here is the final design for getting 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 get the third Monday of the month, add 14 to the second member 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); 

gives the third Monday of the month.

Tested with all months of 2018.

Easier than previous solutions.

The key is the if function on a weekday.

if weekday = 0 (first day is Monday), add 14 days; if on weekday> 0, add 21 days and subtract weekday.

Answer 8

So maybe MORE THAN GENERAL if you can get the "week of the month" for a date using this:

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

which, I believe, gives the exact 0-week index of the week for a given date. then you can use the value to find any nth, as in:

WHERE (week_of_month) = n And day of the week = {day of the week}

You can also use the above to get the "last {day of the week}":

WHERE (week_of_month> = 4) and day of the week = {day of the week}

note that week_of_month can vary from 0 to 5 (for example, a 31-day month, the 1st of which falls on Saturday, will be the 31st of the 6th week (5 as an index based on 0)

hope this helps ...

OK, a little more ... you can define 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 just say something like

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

... I struggled with how 4th / 5th should be returned from WEEKDAY, and decided to add "/ Last" for both according to the theory that it is "good enough" if someone wants to check 4- e, 5th or Past. Using this, you can do:

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

Answer 9

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

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