MySQL 计算年龄

风起未来

已于 2022-08-12 11:09:43 修改

阅读量5.2k

点赞数 1

分类专栏： sql 文章标签： mysql 数据库

于 2022-08-12 11:06:34 首次发布

本文链接：https://blog.csdn.net/qq_34068082/article/details/126300003

版权

sql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

根据出生日期计算年龄

 SELECT DATEDIFF(YEAR,birthday,GETDATE()) as age
 FROM user

报错如下

1582 - Incorrect parameter count in the call to native function 'DATEDIFF'

原因 MySQL DATEDIFF函数只支持两个参数修改为

 SELECT DATEDIFF(birthday,GETDATE()) as age
 FROM user

结果报错

1305 - FUNCTION tendon7.GETDATE does not exist

原因 MySQL 没有 GETDATE 函数

将 GETDATE() 修改为 NOW()

 SELECT DATEDIFF(birthday,NOW()) as age
 FROM user

输出结果为负数这是时间差值以秒为单位的调换参数位置即可

但是为了计算年龄更换函数

SELECT TIMESTAMPDIFF(YEAR,birthday,NOW()) as age
FROM user

附另一种方法：

SELECT 
	IF(DATEDIFF(CURRENT_DATE,CONCAT(DATE_FORMAT(CURRENT_DATE,"%Y"),"-",DATE_FORMAT(birthday,"%m-%d")))>=0,
	DATE_FORMAT(CURRENT_DATE,"%Y") - DATE_FORMAT(birthday,"%Y"),
	DATE_FORMAT(CURRENT_DATE,"%Y") - DATE_FORMAT(birthday,"%Y")-1) as age
FROM user