Column header for a week

from the Artful MySQL Tips List

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)
  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);
delimiter ;
select weeklabel('2011-1-2');  --> 2011-01-02-2011-01-08 

Last updated 4 Jul 2011

Return to the Artful MySQL Tips page