CODEWITHSUNDEEP
mysqlsqlselect
Ann
Ann

Reputation: 377

Get the week of the month in MYSQL

I am developing a Java application using MySQL. I need to know which is the week of each month, of the stored dates. Is there any MySQL function for that ? Basically , if i was to use this for the current date (13.09) it would show me its in week number 2 and tomorrow it will be week number 3.

Upvotes: 4

Views: 10017

Answers (3)

Nadeem Haidar
Nadeem Haidar

Reputation: 11

Create a mysql function. 

CREATE FUNCTION `WEEK_OF_MONTH`(
    datee   DATE
    ) RETURNS INT(11)
BEGIN
    DECLARE DayNamee VARCHAR(20);
    DECLARE StartDatee DATE;
    DECLARE DayNumber INT DEFAULT 0;

    SET DayNamee = (SELECT DAYNAME(datee));
    SET StartDatee = (SELECT FIRST_DAY(datee));

    WHILE StartDatee <= datee DO
        IF DayNamee = DAYNAME(StartDatee) THEN
            SET DayNumber = DayNumber + 1;
        END IF;
        SET StartDatee = DATE_ADD( StartDatee, INTERVAL 1 DAY);
    END WHILE;

    RETURN DayNumber;
END$$

DELIMITER ;

Call as --

SELECT `WEEK_OF_MONTH`('2018-12-31');

Result : 5

Upvotes: 1

fthiella
fthiella

Reputation: 49049

You can play with the WEEK() function, and see if it suits your needs. Here I'm using WEEK(date, 3) that will return the week of the year from 1 to 53, starting from Mondays:

set @my_date = '2015-09-13';

SELECT
  WEEK(@my_date, 3) -
  WEEK(@my_date - INTERVAL DAY(@my_date)-1 DAY, 3) + 1
  AS week_number;
  • WEEK(date, 3) will return the week of the year of the selected date
  • WEEK(date - INTERVAL DAY(@my_date)-1 DAY, 3) will return the week of the year of the first day of the month of the selected date

It will return 1 for 01-March-2015 (because it's the first day of the month so it's week 1) and 2 for 02-March-2015 (because weeks starts from Mondays, so it's a new week). If this is not the desidered behaviour you should specify your requirements more precisely.

Please see a fiddle here.

Upvotes: 7

Mureinik
Mureinik

Reputation: 311088

Unfortunately, there isn't a "weekofmonth" function, but you could use dayofmonth, and manipulate the result a bit:

SELECT CURRENT_DATE(), 
       FLOOR((DAYOFMONTH(CURRENT_DATE()) - 1) / 7) + 1 AS week_of_month

Upvotes: 7

Related Questions