Skip to content

Instantly share code, notes, and snippets.

@AquisTech
Created July 17, 2018 14:26
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save AquisTech/6356df0afab0fd1dff68785e5d0381d3 to your computer and use it in GitHub Desktop.
WEEK OF MONTH function for MySQL to find week number in a month from date. #WEEKOFMONTH #WEEK_OF_MONTH
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;
-- FUNCTION DEFINITION
DELIMITER //
CREATE FUNCTION WEEKOFMONTH ( date DATE )
RETURNS INT
BEGIN
RETURN FLOOR((DAYOFMONTH(date) - 1) / 7) + 1;
END; //
DELIMITER ;
@pishangujeniya
Copy link

pishangujeniya commented Jan 24, 2020

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.

@AquisTech
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment