Question 329362 in Mysql Question posted in
The official MySQL documentation can be found here.

Extract last friday of march month using current month with mysql 8 version

How can I get the date of last friday of march month using current month with mysql 8 version?

Thanks in advance.

My code below… the return is not the last Friday of March, but the last Tuesday of March

SELECT
CASE WHEN MONTH(CURDATE()) = 1 THEN
DATE_ADD(DATE_FORMAT(LAST_DAY(NOW()) - ((7 + WEEKDAY(LAST_DAY(NOW())) - 4) % 7), '%Y-%m-%d'), INTERVAL 2 MONTH) ELSE NULL END last_friday_march_month;
+-------------------------+
| last_friday_march_month |
+-------------------------+
| 2024-03-26              |
+-------------------------+
1 row in set (0.06 sec)
Tags:

2

Answers


  1. 0

    The only thing you need from the current date is a year, so I’d suggest writing it like this:

    SET @year = YEAR(CURDATE());
SET @last_day_march = LAST_DAY(CONCAT(@year, '-03-01'));
SELECT
CASE WHEN MONTH(CURDATE()) = 1 THEN
DATE_SUB(@last_day_march, INTERVAL ((WEEKDAY(@last_day_march) + 3) % 7) DAY)
ELSE NULL END AS last_friday_march_month;
    Login or Signup to reply.
  2. 0

    using a recursive cte

    WITH RECURSIVE CTE (ENDDATE) AS
(SELECT CONCAT(YEAR(NOW()),'-03-31')
UNION ALL 
SELECT DATE_SUB(ENDDATE,INTERVAL 1 DAY)
FROM CTE
WHERE DATE_SUB(ENDDATE,INTERVAL 1 DAY) > DATE_SUB('2024-03-31',INTERVAL 6 DAY)
)
SELECT *,DAYNAME(ENDDATE)  FROM CTE WHERE DAYNAME(ENDDATE) = 'FRIDAY';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search