"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 2024 ...
SET @weekno = Month CurDate() );
SET @date = AddDate('2025-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 |
+------------+------+------------+-------+
| 2024-12-03 | 5 | 2024-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 );
|
|