根据周数，获取周一至周日的数据

创建函数：

CREATE FUNCTION GetDayByWeek (

      # 周数 例：'201845'

weekNum VARCHAR (10),

      #日期所在天数 1-7 表示周一至周日

dayNum INT

) RETURNS DATE

BEGIN

/** *weekNum : '201649' *dayNum : 1-7 对应周一到周日 */

 

DECLARE yearN VARCHAR (4);

 

 

DECLARE weekN VARCHAR (2);

 

 

SET yearN = LEFT (weekNum, 4);

 

 

SET weekN = RIGHT (weekNum, 2);

 

 

SET dayNum = ifnull(dayNum, 1);

 

SELECT

ADDDATE(

 

IF (

WEEK (MAKEDATE(yearN, 1), 3) = 1,

MAKEDATE(yearN, 1),

ADDDATE(MAKEDATE(yearN, 1), 7)

),

(weekN - 1) * 7 + dayNum - 1 - WEEKDAY(MAKEDATE(yearN, 1))

) INTO @result;

 

RETURN @result;

 

检索：

select GetDayByWeek('201835',1)