Reports often have to lay out weekly data. Here is a function which, passed date d, returns a string comprising ...
- the date of the first day in the week of
d which is in the year of d
- a dash
- the date of the last day in the week of
d which is in the year of d:
set global log_bin_trust_function_creators=1;
drop function if exists weeklabel;
delimiter go
create function weeklabel( d date ) returns char(24)
begin
declare v, day, w, yr, yr1, yr2 smallint;
declare first, last char(16);
set w = week(d);
set day = dayofweek(d);
set yr = year(d);
set first = if( day=1, d, date_sub(d,interval day-1 day) );
set yr1 = year(first);
set last = date_add(first,interval 6 day);
set yr2 = year(last);
set first = if( yr1=yr, first, concat(yr, '-01-01' ));
set last = if( yr2=yr, last, concat(yr,'-12-31' ));
return concat(first,'-',last);
end;
go
delimiter ;
select weeklabel('2025-1-2'); --> 2011-01-02-2011-01-08
|
|