Share 一个昨天写的函数。 目的是求给定的时间是所在月份的第几个礼拜。
演示下结果:
SELECT weekofmonth(NOW()) AS result;
query result:
result
4
- DELIMITER $$
- USE `t_girl`$$
- DROP FUNCTION IF EXISTS `weekofmonth`$$
- CREATE DEFINER=`root`@`localhost` FUNCTION `weekofmonth`(
- f_datetime DATETIME
- ) RETURNS INT(11)
- BEGIN
- -- Created by ytt.
- DECLARE v_result INT;
- DECLARE v_weekno INT;
- SET v_weekno =
- CASE DAYOFWEEK(f_datetime)
- WHEN 1 THEN 7
- WHEN 2 THEN 1
- WHEN 3 THEN 2
- WHEN 4 THEN 3
- WHEN 5 THEN 4
- WHEN 6 THEN 5
- WHEN 7 THEN 6
- END;
- SET v_result = CEIL(DAYOFMONTH(DATE_SUB(f_datetime,INTERVAL v_weekno DAY))/7)+1;
- RETURN v_result;
- END$$
- DELIMITER ;
演示下结果:
SELECT weekofmonth(NOW()) AS result;
query result:
result
4