MYSQL – Retrieving weeks from month

MySQL

I'm trying to list all weeks in a month, (e.g., 2015-02-01 to 2015-02-28) in the following format:

enter image description here

Tried using WEEK e.g., SELECT WEEK(2015-02-01) – WEEK(2015-02-28); but this just gave me the number of numbers in the month – 4.

What is the proper MYSQL statement to achieve this

Best Answer

I have written a stored routines called month_weeks. It accepts two parameters, the month and the year and output a list of weeks. It uses a temporary table to calculate the start and the end of weeks. For example:

call month_weeks(1,2015);

returns:

# WeekFrom, WeekTo
2014-12-29, 2015-01-04
2015-01-05, 2015-01-11
2015-01-12, 2015-01-18
2015-01-19, 2015-01-25
2015-01-26, 2015-02-01

The text of routine is:

DELIMITER $$
CREATE PROCEDURE month_weeks(
  IN month INT,
  IN year INT)
BEGIN
  drop table if exists tmp;
  create temporary table tmp(WeekFrom date, WeekTo date) engine=memory;
  set @s_month:=STR_TO_DATE( concat('01-', convert(month, char), '-', convert(year, char)),'%d-%m-%Y');  
  set @e_month= last_day(@s_month);

  set @s_week:=date_add(@s_month, interval (-dayofweek(@s_month)+2) DAY);
  set @e_week:=date_add(@s_week, interval 6 DAY);  
  while datediff(@e_month, @s_week) >= 0 do
      insert into tmp values(@s_week, @e_week);
      set @s_week:=date_add(@s_week, interval 7 DAY);
      set @e_week:=date_add(@e_week, interval 7 DAY);  
  end while;
  select * from tmp;
END $$
delimiter ;
Related Question