mysql_计算周年的两种方法

作者：YXN-sql 阅读量：68 发布日期：2023-04-19

-- 1、计算周岁（今天不计算在内，如今天是2023-12-03，算2020-12-03是2年，2020-12-02是3年）
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),birth_date)),'%Y')+0
# 举例：
SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF('2023-12-03','2020-12-03')),'%Y')+0
# 输出： 2

-- 2、该存储过程为计算周岁，今天计算在内，如今天2023-12-03，算2020-12-04是2年，2020-12-03是3年
DELIMITER //

CREATE FUNCTION Calculate_Age(birthdate DATE, currentdate DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE age INT;
    DECLARE birth_month INT;
    DECLARE birth_day INT;
    DECLARE current_month INT;
    DECLARE current_day INT;

    SET age = YEAR(currentdate) - YEAR(birthdate);
    SET birth_month = MONTH(birthdate);
    SET birth_day = DAY(birthdate);
    SET current_month = MONTH(currentdate);
    SET current_day = DAY(currentdate);

    IF current_month < birth_month OR (current_month = birth_month AND current_day < birth_day) THEN
        SET age = age - 1;
    END IF;

    RETURN age;
END //

DELIMITER ;

-- 调用该函数
SELECT Calculate_Age(birth_date, CURDATE()) AS age FROM table1;
# 举例
SELECT Calculate_Age('2020-12-03', '2023-12-03') AS age -- 输出：3
SELECT Calculate_Age('2020-12-04', '2023-12-03') AS age -- 输出：2

YXN-sql

2023-04-19