方法一 当前时间天数-生日天数–转换年份
当前时间为 2020-07-04
set @dt = STR_TO_DATE(‘1991-09-29’,’%Y-%m-%d’); – 1991-09-29
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(@dt)), ‘%Y’)+0 AS age; – 28
此方法缺陷,就是当日期为未来日期时结果为0,而不是负数
set @dt1 = STR_TO_DATE(‘2021-09-29’,’%Y-%m-%d’); – 2021-09-29
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(@dt1)), ‘%Y’)+0 AS age; – 0
方法二
当前时间为 2020-07- 04
set @dt1 = STR_TO_DATE(‘2021-09-29’,’%Y-%m-%d’); – 2021-09-29
SELECT DATE_FORMAT(NOW(), ‘%Y’) - DATE_FORMAT(@dt1, ‘%Y’) - (DATE_FORMAT(NOW(), ‘00-%m-%d’) <DATE_FORMAT(@dt1, ‘00-%m-%d’)) AS age
改良后方法一和方法二
SELECT year( from_days( datediff( now( ), birthdate)));
SELECT YEAR(CURDATE())-YEAR(birthday)-(RIGHT(CURDATE(),5)-RIGHT(birthday,5));
SELECT year( from_days( datediff( now( ), birthdate)));
SELECT YEAR(CURDATE())-YEAR(birthday)-(RIGHT(CURDATE(),5)<RIGHT(birthday,5));
改良后的方法一,少了一个函数和一个运算符,当日期为未来日期时计算结果还是为0;
改良后的方法二,还是6个函数和3个运算符,看起来简单些;取日期的右边五位,当日期格式为‘2013-01-01’时取到的是‘01-01’,没有问题;当日期格式为‘2013-1-1’缩写格式时,取右边的五位取出的是‘3-1-1’,会导致出错。
范例:
set @dt = STR_TO_DATE(‘1991-09-29’,’%Y-%m-%d’); – 1991-09-29
SELECT year( from_days( datediff( now( ), @dt)));
改良后的方法一,当日期为未来日期时计算结果还是为0;
set @dt1 = STR_TO_DATE(‘2021-12-29’,’%Y-%m-%d’); – 2021-09-29
SELECT year( from_days( datediff( now( ), @dt1)));
set @dt1 = STR_TO_DATE(‘1991-09-29 00:00:00’,’%Y-%m-%d’); – 2021-09-29
SELECT YEAR(CURDATE())-YEAR(@dt1)-(RIGHT(CURDATE(),5)<RIGHT(@dt1,5));
set @dt1 = STR_TO_DATE(‘2021-09-29 00:00:00’,’%Y-%m-%d’);
SELECT YEAR(CURDATE())-YEAR(@dt1)-(RIGHT(CURDATE(),5)<RIGHT(@dt1,5));
方法三
SELECT FLOOR(DATEDIFF(CURDATE(), @birthday)/365.2422)
取生日和当前日期之前的天数除以一年的实际天数(365天5小时48分46秒),然后取整。
范例:
set @dt1 = STR_TO_DATE(‘1991-09-29 00:00:00’,’%Y-%m-%d’);
SELECT FLOOR(DATEDIFF(CURDATE(), @dt1)/365.2422) ;
方法四
SELECT TIMESTAMPDIFF(YEAR, @birthday, CURDATE())
测试了一下以上四种方法,假如当前日期为’2017-1-13’,当生日为‘2013-1-14’时,还差一天就要过生日了,离4岁只 差一天了,结果还是3岁,感觉不是很合理;把方法三改造一下,四舍五入得到方法五:
范例:
set @dt1 = STR_TO_DATE(‘1991-09-29 00:00:00’,’%Y-%m-%d’);
SELECT TIMESTAMPDIFF(YEAR, @dt1, CURDATE()) ;
方法五
SELECT ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422)
范例:
set @dt1 = STR_TO_DATE(‘1991-09-29 00:00:00’,’%Y-%m-%d’);
SELECT ROUND(DATEDIFF(CURDATE(), @dt1)/365.2422) ;
综上,年龄计算一般采用方法四或者方法五