Mysql 计算月龄自定义函数

keyliwen

于 2018-04-25 17:38:09 发布

阅读量783

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： MySQL SQL

本文链接：https://blog.youkuaiyun.com/keyliwen/article/details/80083039

MySQL 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个用于计算两个日期间月份数差的SQL函数。该函数通过比较两个TIMESTAMP类型的日期，返回它们之间的月份数差异。具体实现包括确定两个日期的年份、月份和天数，再进行相应的计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

delimiter //
DROP FUNCTION IF EXISTS monthdiff;
CREATE FUNCTION monthdiff(first TIMESTAMP, second TIMESTAMP)
RETURNS INTEGER
BEGIN
DECLARE tmp TIMESTAMP;
DECLARE yearOfFirst INT;
DECLARE yearOfSecond INT;
DECLARE monthOfFirst INT;
DECLARE monthOfSecond INT;
DECLARE dayOfFirst INT;
DECLARE dayOfSecond INT;
DECLARE yearInterval INT;
DECLARE monthInterval INT;
IF first < second THEN
SET tmp=first;
SET first=second;
SET second=tmp;
END IF;
SET yearOfFirst = YEAR(first);
SET yearOfSecond = YEAR(second);
SET monthOfFirst = MONTH(first);
SET monthOfSecond = MONTH(second);
SET dayOfFirst = DAYOFMONTH(first);
SET dayOfSecond = DAYOFMONTH(second);
SET yearInterval = yearOfFirst - yearOfSecond;
IF monthOfFirst < monthOfSecond OR (monthOfFirst = monthOfSecond AND dayOfFirst < dayOfSecond) THEN
SET yearInterval = yearInterval - 1;
END IF;
SET monthInterval = (monthOfFirst + 12) - monthOfSecond;
IF (dayOfFirst < dayOfSecond) THEN
SET monthInterval = monthInterval - 1;
END IF;
SET monthInterval = monthInterval % 12;
RETURN yearInterval * 12 + monthInterval;
END//
delimiter;