mysql通过指定周数（IOS周）获取日期

创建：

DELIMITER //
CREATE FUNCTION GetDateByWeek(in_yearweek VARCHAR(10) , in_dayofweek int )
RETURNS DATE
BEGIN
/**
*in_yearweek : '201649'
*in_dayofweek : 1-7 对应周一到周日
*/
declare i_year  VARCHAR(4);
declare i_week  VARCHAR(2);
set i_year = left( in_yearweek ,4);
set i_week = right( in_yearweek ,2);
set in_dayofweek = ifnull(in_dayofweek,1);
select ADDDATE( if( WEEK( MAKEDATE(i_year, 1) , 3)=1,  MAKEDATE(i_year, 1) , ADDDATE( MAKEDATE(i_year, 1),7) ) , (i_week-1)*7 + in_dayofweek-1 -WEEKDAY( MAKEDATE(i_year, 1) )   ) into @result  ;
RETURN @result ;
END //

调用方式 ：
select getDateByWeek('201601',1)

转载于:https://my.oschina.net/u/3088580/blog/812880