CODEWITHSUNDEEP
mysql
Tamás Pap
Tamás Pap

Reputation: 18273

Find the N:th Monday in a month with MySQL

How to find the n-th weekday of a month based on @ref_date, @n, and @week_day in MySQL:

SET @ref_date = '2012-09-25';
SET @n = 3; #The third week
SET @week_day = 0; #Monday

The result should be: 2012-09-17.

Any suggestion appreciated.

Upvotes: 1

Views: 2257

Answers (2)

PinnyM
PinnyM

Reputation: 35533

You have @weekday using the standard '0 == Monday', but MySql uses a 1-based standard with '1 == Sunday'. Assuming we are constrained to say that 0 must equal Monday, you can do this:

SET @first_day = DATE_SUB(@ref_date, INTERVAL DAYOFMONTH(@ref_date) - 1 DAY);
SET @first_day_of_week = (DAYOFWEEK(@first_day) - 2) % 7;
SET @solution = DATE_SUB(DATE_ADD(@first_day, INTERVAL @n WEEK), INTERVAL ( 7 - ((@week_day - @first_day_of_week + 7) % 7) ) DAY);

So as a single query that would be:

SELECT DATE_SUB( DATE_ADD( DATE_SUB( @ref_date, INTERVAL DAYOFMONTH(@ref_date) - 1 DAY),  INTERVAL @n WEEK), INTERVAL ( 7 - ((@week_day - ((DAYOFWEEK(DATE_SUB( @ref_date, INTERVAL DAYOFMONTH(@ref_date) - 1 DAY)) - 2) % 7) + 7) % 7) ) DAY);

Upvotes: 3

xkeshav
xkeshav

Reputation: 54016

find in one of the solution in SO here

SELECT  WEEK(dateField, 5) -
        WEEK(DATE_SUB(dateField, INTERVAL DAYOFMONTH(dateField) - 1 DAY), 5) + 1

Read Week of the Month in Mysql

Upvotes: 1

Related Questions