What month does a week fall in?

from the Artful Common Queries page


"The" month of a week is ambiguous if the week straddles months. If we adopt the convention that the month of a week is the month of its beginning Sunday, then on 29 Nov 2009 ...

SET @weekno = Month CurDate() );
SET @date = AddDate('2009-01-01', 7*@weekno );
SET @day = DayOfWeek( @date );
SET @datecomp = IF( @day = 1, @date, AddDate( @date, 1-@day ));
SELECT @date,@day,@datecomp,Month(@datecomp) AS month;
+------------+------+------------+-------+
| @date      | @day | @datecomp  | month |
+------------+------+------------+-------+
| 2009-12-03 |    5 | 2009-11-29 |    11 |
+------------+------+------------+-------+

It's easy to encapsulate into a stored function. We might as well parameterise the daynumber which the function is to use as a criterion:

DROP FUNCTION IF EXISTS MonthOfWeek;
SET GLOBAL log_bin_trust_function_creators=1;
DELIMITER go
CREATE FUNCTION MonthOfWeek( pyear YEAR, pweek SMALLINT, pday SMALLINT ) RETURNS SMALLINT
BEGIN
  DECLARE vdate DATE;
  DECLARE vday SMALLINT;
  SET vdate = AddDate(Concat( pyear, '-01-01'), 7*pweek );
  SET vday = DayOfWeek( vdate );
  SET vdate = IF( vday = 1, vdate, AddDate( vdate, 1-vday ));
  RETURN Month( vdate );
END;
go
DELIMITER ;
SELECT MonthOfWeek( Year(CurDate()), Week( CurDate() ), 1 );


Return to the Artful Common Queries page