mysql 去年同星期_Mysql自定义函数返回去年同周的周一

mysql自定义函数

## 返回in_day 所在周的星期一的日期

DELIMITER $$

CREATE FUNCTION get_first_day_of_week(in_day DATE)

RETURNS DATE

BEGIN

DECLARE l_numofweek  INT;

DECLARE l_dayofweek  DATE;

SET l_numofweek = (DAYOFWEEK(in_day) - 2 + 7) % 7;

SET l_dayofweek = DATE_SUB(in_day , INTERVAL l_numofweek DAY );

RETURN l_dayofweek;

END;

$$

DELIMITER ;

##  i_day为一年中的第n周

##  返回去年的第n周的周一的日期

DELIMITER $$

CREATE FUNCTION get_first_of_week_last_year (i_day DATE) RETURNS DATE

BEGIN

DECLARE l_week_of_year INT ;

DECLARE l_last_year VARCHAR (100) ;

DECLARE l_day DATE ;

SET l_week_of_year = WEEKOFYEAR(i_day) ;

SET l_last_year = CONCAT(

(DATE_FORMAT(i_day, '%Y') - 1),

'-01-01'

) ;

SET l_day = STR_TO_DATE(l_last_year, '%Y-%m-%d') ;

SET l_day = get_first_day_of_week (l_day) ;

SET l_day = DATE_ADD(

l_day,

INTERVAL l_week_of_year - 1 WEEK

) ;

RETURN l_day ;

END ;

$$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29121762/viewspace-1672044/，如需转载，请注明出处，否则将追究法律责任。