WEEK OF MONTH function for MySQL to find week number in a month from date. #WEEKOFMONTH #WEEK_OF_MONTH

test_queries.sql

SELECT '2018-07-1', WEEKOFMONTH('2018-07-1') AS week_of_month;
SELECT '2018-07-5', WEEKOFMONTH('2018-07-5') AS week_of_month;
SELECT '2018-07-7', WEEKOFMONTH('2018-07-7') AS week_of_month;

SELECT '2018-07-8', WEEKOFMONTH('2018-07-8') AS week_of_month;
SELECT '2018-07-10', WEEKOFMONTH('2018-07-10') AS week_of_month;
SELECT '2018-07-14', WEEKOFMONTH('2018-07-14') AS week_of_month;

SELECT '2018-07-15', WEEKOFMONTH('2018-07-15') AS week_of_month;
SELECT '2018-07-20', WEEKOFMONTH('2018-07-20') AS week_of_month;
SELECT '2018-07-21', WEEKOFMONTH('2018-07-21') AS week_of_month;

SELECT '2018-07-22', WEEKOFMONTH('2018-07-22') AS week_of_month;
SELECT '2018-07-25', WEEKOFMONTH('2018-07-25') AS week_of_month;
SELECT '2018-07-28', WEEKOFMONTH('2018-07-28') AS week_of_month;

SELECT '2018-07-29', WEEKOFMONTH('2018-07-29') AS week_of_month;
SELECT '2018-07-30', WEEKOFMONTH('2018-07-30') AS week_of_month;
SELECT '2018-07-31', WEEKOFMONTH('2018-07-31') AS week_of_month;

week_of_month.sql

-- FUNCTION DEFINITION
DELIMITER //

CREATE FUNCTION WEEKOFMONTH ( date DATE )
RETURNS INT

BEGIN

   RETURN FLOOR((DAYOFMONTH(date) - 1) / 7) + 1;

END; //

DELIMITER ;

Output is not correct for January 2020 first week and January 2020 last week.
You also need to take care the starting day of the week of the month.

Output is not correct for January 2020 first week and January 2020 last week.
You also need to take care the starting day of the week of the month.

Sorry for late reply @pishangujeniya.
You are right. I got those issues but then I stopped maintaining MYSQL functions. I started using Postgres for that project so wrote functions in PG