cms9651
January 28, 2024, 10:10am
1
How can I get the date of first friday of march month using current month with mysql 8 version?
Thanks in advance.
My code below… the return is not the first 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)
r937
January 28, 2024, 11:47am
2
before i try my hand at a solution, may i ask for more information?
do you want to run this ~only~ in January? or is your CASE expression going to need a formula for every month? if you run it in April, do you want the last Friday of the previous March or the next one?
cms9651
January 28, 2024, 12:16pm
3
Thanks for the reply.
I apologize but there was a misunderstanding…
I need get the date of first (not last) friday for each month… executing according to this scheme…
Month
Day
JANUARY
FIRST FRIDAY OF MARCH
FEBRUARY
FIRST FRIDAY OF APRIL
MARCH
FIRST FRIDAY OF MAY
APRIL
FIRST FRIDAY OF JUNE
MAY
FIRST FRIDAY OF JULY
JUNE
FIRST FRIDAY OF AUGUST
JULY
FIRST FRIDAY OF SEPTEMBER
AUGUST
FIRST FRIDAY OF OCTOBER
SEPTEMBER
FIRST FRIDAY OF NOVEMBER
OCTOBER
FIRST FRIDAY OF DECEMBER
NOVEMBER
FIRST FRIDAY OF JANUARY 2025
DECEMBER
FIRST FRIDAY OF FEBRUARY 2025
r937
January 28, 2024, 1:47pm
4
here’s the formula you want (using CURRENT_DATE as the start point)
SELECT
LAST_DAY( LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY )
+ INTERVAL
( 12 - DAYOFWEEK(
LAST_DAY( LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY )
) ) % 7 + 1 DAY AS first_friday
fully tested here – https://www.db-fiddle.com/f/kMFzaK17yA2zXoQ4b9RHuT/0
1 Like
Wow, thanks buddy!
Your help really appreciated
system
Closed
April 28, 2024, 9:52pm
6
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.