Problem Description:

I need a MySQL query that, given a date (e.g., '2025-02-10'), returns the last occurrence of the same weekday in the current quarter. However, if the given date has already passed that occurrence, it should return the same weekday in the next quarter.

Logic:

1. Determine the weekday of the given date (Monday, Tuesday, etc.).
2. Find the last occurrence of that same weekday in the current quarter.
3. If the given date has already passed that occurrence, return the same weekday from the next quarter instead.

Example Scenarios:

Problem Description:

I need a MySQL query that, given a date (e.g., '2025-02-10'), returns the last occurrence of the same weekday in the current quarter. However, if the given date has already passed that occurrence, it should return the same weekday in the next quarter.

Logic:

1. Determine the weekday of the given date (Monday, Tuesday, etc.).
2. Find the last occurrence of that same weekday in the current quarter.
3. If the given date has already passed that occurrence, return the same weekday from the next quarter instead.

Example Scenarios:

What I Have Tried:

I wrote a query to get the last occurrence of a specific weekday in a quarter, but I need help modifying it to dynamically pick the same weekday as the given date and handle cases where the date has already passed.

SET @target_weekday = 0; -- Monday (0 = Monday, ..., 6 = Sunday)

SELECT date,
    CASE
        WHEN QUARTER(date) = 1 THEN DATE_SUB(
            STR_TO_DATE(CONCAT(YEAR(date), '-03-31'), '%Y-%m-%d'),
            INTERVAL (WEEKDAY(STR_TO_DATE(CONCAT(YEAR(date), '-03-31'), '%Y-%m-%d')) - @target_weekday + 7) % 7 DAY
        )
        WHEN QUARTER(date) = 2 THEN DATE_SUB(
            STR_TO_DATE(CONCAT(YEAR(date), '-06-30'), '%Y-%m-%d'),
            INTERVAL (WEEKDAY(STR_TO_DATE(CONCAT(YEAR(date), '-06-30'), '%Y-%m-%d')) - @target_weekday + 7) % 7 DAY
        )
        WHEN QUARTER(date) = 3 THEN DATE_SUB(
            STR_TO_DATE(CONCAT(YEAR(date), '-09-30'), '%Y-%m-%d'),
            INTERVAL (WEEKDAY(STR_TO_DATE(CONCAT(YEAR(date), '-09-30'), '%Y-%m-%d')) - @target_weekday + 7) % 7 DAY
        )
        WHEN QUARTER(date) = 4 THEN DATE_SUB(
            STR_TO_DATE(CONCAT(YEAR(date), '-12-31'), '%Y-%m-%d'),
            INTERVAL (WEEKDAY(STR_TO_DATE(CONCAT(YEAR(date), '-12-31'), '%Y-%m-%d')) - @target_weekday + 7) % 7 DAY
        )
    END AS last_specified_day_of_quarter
FROM (SELECT '2025-02-10' AS date) AS temp;

Issue I Need Help With:

1. Correctly checking whether the date has passed and switching to the next quarter.
2. Ensuring it dynamically picks the same weekday as the given date instead of a hardcoded day like Sunday.

• sql
• mysql
• mariadb

• You can use the WEEKDAY() function to get the weekday of a given date, e.g. SET @target_weekday = WEEKDAY('2025-02-10') – Barmar Commented Feb 5 at 16:14
• I suggest you write two queries, one to get the date in the current quarter, another to get the date in the next quarter. Combine them with UNION and then select the earliest result after the given date. – Barmar Commented Feb 5 at 16:17
• if possible , will you please provide complete query @Barmar – Ranjan Commented Feb 5 at 16:29
• I think you mean : if the given date has already passed or reached , don't you ? – Barbaros Özhan Commented Feb 5 at 17:06
• 1 "given a date (e.g., '2025-02-10'), returns the last occurrence of the same weekday in the current quarter. However, if the given date has already passed that occurrence" -- sounds impossible. Consider reforumlating the problem. – Rick James Commented Feb 5 at 18:45

5 Answers 5

Based on @Barmar's suggestion, I tried the following steps:

1. First find the weekday of the given date

    SET @given_date := '2025-12-29';
    SET @target_weekday := WEEKDAY(@given_date);

2. Then the start and end date of current and next quarter is fetched.

3. An intermediate sequence between 0-91 is generated to iterate through the days in current and next quarter.

4. And then there are two queries to find last occurrence of the same weekday for current and next quarter.

5. Last step is to find the earliest date for the given date by combining the resultset from step4.

For given date

2025-12-29

it generates

output
2026-03-30 

Last occurrence of the same weekday in current quarter

WITH RECURSIVE seq AS (
    SELECT 0 AS seq
    UNION ALL
    SELECT seq + 1
    FROM seq
    WHERE seq < 92
)

 SELECT @last_weekday_current_quarter := MAX(date)
    FROM (
        SELECT 
            DATE_ADD(@current_quarter_start, INTERVAL seq DAY) AS date 
        FROM seq 
        WHERE WEEKDAY(DATE_ADD(@current_quarter_start, INTERVAL seq DAY)) = @target_weekday
        AND DATE_ADD(@current_quarter_start, INTERVAL seq DAY) <= @current_quarter_end
    ) AS t;

Last occurrence of the same weekday in next quarter

WITH RECURSIVE seq AS (
    SELECT 0 AS seq
    UNION ALL
    SELECT seq + 1
    FROM seq
    WHERE seq < 92
)

SELECT @last_weekday_next_quarter := MAX(date)
FROM (
    SELECT 
        DATE_ADD(@next_quarter_start, INTERVAL seq DAY) AS date 
    FROM seq 
    WHERE WEEKDAY(DATE_ADD(@next_quarter_start, INTERVAL seq DAY)) = @target_weekday
    AND DATE_ADD(@next_quarter_start, INTERVAL seq DAY) <= @next_quarter_end
) AS t;

Above two output is combined to find the earliest date

SELECT MIN(result) AS output
FROM (
    SELECT @last_weekday_current_quarter AS result
    UNION ALL
    SELECT @last_weekday_next_quarter AS result
) AS combined_results
WHERE result > @given_date;

Fiddle Demo

--      S a m p l e    D a t a :
Create Table dates( reff_date Date );
Insert Into dates
      ( Select STR_TO_DATE('2025-02-10', '%Y-%m-%d') as reff_date Union All
        Select STR_TO_DATE('2025-04-10', '%Y-%m-%d') Union All
        Select STR_TO_DATE('2025-12-29', '%Y-%m-%d') 
      );
Select  *
From    dates;

Create a recursive cte (cal) to generate rows like a calendar of the same day of the week like your refference date from the sample data. Make sure for the calendar to span enough to contain the whole quarter.

WITH RECURSIVE 
    cal ( reff_date, day_id, Q, DOW ) as
      ( Select  reff_date, 
                reff_date, 
                QUARTER(reff_date),
                DAYOFWEEK(reff_date)
        From dates
      UNION ALL
        Select  c.reff_date, 
                c.day_id + INTERVAL '7' DAY, 
                QUARTER(c.day_id + INTERVAL '7' DAY),
                DAYOFWEEK(c.day_id + INTERVAL '7' DAY)
        From   cal c
        Where  c.day_id < reff_date + INTERVAL '92' DAY
     )

Now, in inner query do the first (Max) aggregation to get the last dates for the same day of week - group by reff_date, quarter and day of week.
In the outer query do the opposite (Min) aggregation group by reff_date and filter using Where clause to get the date greater than your refference date...

--      M a i n    S Q L : 
SELECT   reff_date, Min(max_date) as last_same
FROM   ( Select    reff_date, Q, DOW, Max(day_id) as max_date
         From      cal
         Group By reff_date, Q, DOW
       ) x
WHERE reff_date < max_date
GROUP BY reff_date
ORDER BY reff_date

fiddle

We can directly calculate target date.
See example

select given_date
  ,dayname(given_date) Weekday_of_Given_Date
  ,case when td1<>given_date then td1 else td2 end target_date
  ,dayname(case when td1<>given_date then td1 else td2 end) target_date_name
from( -- target dates for quarters
  select *
    ,date_add(eqt1,interval+(-dweqt1+dw0 -case when dweqt1>=dw0 then 0 else 7 end) day) td1
    ,date_add(eqt2,interval+(-dweqt2+dw0 -case when dweqt2>=dw0 then 0 else 7 end) day) td2
  from(-- week day for end of quarter
    select given_date, dw0
      ,eqt1 ,dayofweek(eqt1) dweqt1
      ,eqt2 ,dayofweek(eqt2) dweqt2
    from( -- end of quarters
      select given_date,dayofweek(given_date) dw0
        , date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval qq*3-mm+1 month)
           ,interval -1 day) eqt1 
        ,date_add(date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval qq*3-mm+1 month)
           ,interval -1 day)
           ,interval 1 quarter) eqt2
      from (select *,month(t.given_date) mm,quarter(t.given_date) qq from test t) dd
    )q1
  )q2
)q3

Step by step:

Calculate end of quarter (eqt1) and end of next quarter (eqt2) as

• first day of this month
• first day of first month of next quarter (qq*3-mm+1)
• shift back 1 day

For example for date '2025-02-10'

• first day of month '2025-02-10' - interval 10 day +interval 1 day=2025-02-01
• first day of first month of next quarter '2025-02-01' +interval (1*3 -2+1)=2 month ->'2025-04-01'. There quarter qq=1, month mm=2.
• shift back interval 1 day '2025-04-01'->'2025-03-31'

    date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval qq*3-mm+1 month)
           ,interval -1 day) eqt1 
   date_add(date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval qq*3-mm+1 month)
           ,interval -1 day)
           ,interval 1 quarter) eqt2

and dayofweek for last day of quarters dayofweek(eqt1) wdeqt1.

Then calculate target day for each quarter as

   ,date_add(eqt1,interval+(-wdeqt1+dw0 -case when wdeqt1>=dw0 then 0 else 7 end) day) td1
   ,date_add(eqt2,interval+(-wdeqt2+dw0 -case when wdeqt2>=dw0 then 0 else 7 end) day) td2
 

Target day is shifted back from end of quarter as

interval+(-wdeqt1+dw0 -case when wdeqt1>=dw0 then 0 else 7 end) day)

For example

calculate (-wdeqt1+dw0 -case when wdeqt1>=dw0 then 0 else 7 end) as (-4+5- case when 4>=5 then 0 else 7 end)=-6. So '2025-21-31'-interval 6 day =>'2025-12-25'.
Shortly:

• we shift back from end of quarter to dayofweek for given_date - dayofweek for end of quarter
• if dayofweek for given_date > dayofweek for end of quarter, we shift back additional 1 week.

fiddle

Query example for 1 date, passed as variable (parameter)

set @given_date =cast('2025-02-10' as date);
select given_date
  ,dayname(given_date) Weekday_of_Given_Date
  ,case when td1<>given_date then td1 else td2 end target_date
  ,dayname(case when td1<>given_date then td1 else td2 end) target_date_name
from(
  select *
    ,date_add(eqt1,interval+(-dweqt1+dw0 -case when dweqt1>=dw0 then 0 else 7 end) day) td1
    ,date_add(eqt2,interval+(-dweqt2+dw0 -case when dweqt2>=dw0 then 0 else 7 end) day) td2
  from(
    select given_date, dw0
      ,eqt1 ,dayofweek(eqt1) dweqt1
      ,eqt2 ,dayofweek(eqt2) dweqt2
    from(
      select given_date,dayofweek(given_date) dw0
        , date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval qq*3-mm+1 month)
           ,interval -1 day) eqt1 
        ,date_add(date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval qq*3-mm+1 month)
           ,interval -1 day)
           ,interval 1 quarter) eqt2
      from ( select @given_date as given_date,month(@given_date) mm,quarter(@given_date) qq )d
    )q1
  )q2
)q3

Steps:

1. Calculate end of quarter (eqt1) and end of next quarter (eqt2)
2. Calculate full weeks count between given_date and end_of_quarter.
3. Add full weeks days to given_date -> this is target date for quarter (td1 and td2)
4. Select target_date for current or next quarter (target_date)

See example

set @given_date =cast('2025-02-10' as date);

select given_date
  ,dayname(given_date) Weekday_of_Given_Date
  ,case when td1<>given_date then td1 else td2 end target_date
  ,dayname(case when td1<>given_date then td1 else td2 end) target_date_dayname
from( -- add full weeks to given_date ->target dates for quarters
    select given_date
      ,date_add(given_date,interval+(datediff(eqt1,given_date) div 7)*7 day) td1
      ,date_add(given_date,interval+(datediff(eqt2,given_date) div 7)*7 day) td2
    from( -- end of quarters
      select given_date,dayofweek(given_date) dw0
        , date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval quarter(given_date)*3-month(given_date)+1 month)
           ,interval -1 day) eqt1 
        ,date_add(date_add(date_add(date_add(given_date, interval -day(given_date)+1 day)
           ,interval quarter(given_date)*3-month(given_date)+1 month)
           ,interval -1 day)
           ,interval 1 quarter) eqt2
      from (select @given_date as given_date)dd
    )q1
)q2

fiddle

Maybe a little shorter

set @given_date =cast('2025-12-29' as date);
select given_date
  ,dayname(given_date) Weekday_of_Given_Date
  ,case when td1<>given_date then td1 else td2 end target_date
  ,dayname(case when td1<>given_date then td1 else td2 end) target_date_name
from( -- target dates for quarters
    select given_date
      ,date_add(given_date,interval (datediff(eqt1,given_date) div 7)*7 day) td1
      ,date_add(given_date,interval (datediff(date_add(eqt1,interval 1 quarter),given_date) div 7)*7 day) td2
    from( -- end of quarter1
      select @given_date as given_date
        ,date_add(date_add(date_format(@given_date,'%y-01-01')
                 ,interval quarter(@given_date) quarter), interval -1 day) as eqt1
    )dd
)q2

fiddle

This is just some straightforward calculation, no recursive queries or cases for different quarters needed.

Given a date:

set @input_date = '2025-03-31';

Find the date of the last day in the current^* quarter:

concat(year(curdate()),'-01-01')
    + interval quarter(curdate()) quarter
    - interval 1 day

And adjust it by how many weekdays that is later, which is the weekday of the last day of the quarter minus the weekday of the input date, adjusted to the 0-6 range:

- interval (
    weekday(concat(year(curdate()),'-01-01') + interval quarter(curdate()) quarter - interval 1 day)
    - weekday(@input_date)
    + 7
) % 7 day

Then if that results in a date before @input_date, retry all of that using the input date instead of the current date. So:

case when @input_date <
    concat(year(curdate()),'-01-01')
        + interval quarter(curdate()) quarter
        - interval 1 day
        - interval (weekday(concat(year(curdate()),'-01-01') + interval quarter(curdate()) quarter - interval 1 day) - weekday(@input_date) + 7) % 7 day
then
    concat(year(curdate()),'-01-01')
        + interval quarter(curdate()) quarter
        - interval 1 day
        - interval (weekday(concat(year(curdate()),'-01-01') + interval quarter(curdate()) quarter - interval 1 day) - weekday(@input_date) + 7) % 7 day
else
    concat(year(@input_date),'-01-01')
        + interval quarter(@input_date) quarter
        - interval 1 day
        - interval (weekday(concat(year(@input_date),'-01-01') + interval quarter(@input_date) quarter - interval 1 day) - weekday(@input_date) + 7) % 7 day
end

^{*_{curdate used for brevity; I recommend avoiding curdate and all other functions that can return different results based on the timezone set by the client, and just explicitly calculating the date in the timezone you want. For instance, for US Eastern time, replace curdate() with date(convert_tz(utc_timestamp(), '+00:00', 'America/New_York')).}}