mysql - 从当月开始计算年龄

Question

我有以下代码可以从今天的日期计算一个人的出生日期，效果很好

    SELECT application.applicationLastName, application.applicationFirstName, application.applicationDoB, 
ROUND(DATEDIFF(NOW(), applicationDoB) / 365.2425,1) AS age, application.applicationID, application.applicationAcknowledged, application.applicationContacted, application.applicationDate
FROM application
ORDER BY application.applicationDate ASC 

但是，我被要求从当月 1 号开始计算他们的年龄，并尝试过

SELECT application.applicationLastName, application.applicationFirstName, application.applicationDoB, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS CURMONTH, 
ROUND(DATEDIFF(CURMONTH(), applicationDoB) / 365.2425,1) AS age, application.applicationID, application.applicationAcknowledged, application.applicationContacted, application.applicationDate
FROM application
ORDER BY application.applicationDate ASC

但是我收到以下错误:调用 native 函数“DATEDIFF”时参数计数不正确

Answer 1

您不应使用CURMONTH()。而是将今天的日期格式设置为本月 1 日。

使用以下查询:

SELECT application.applicationLastName, application.applicationFirstName, application.applicationDoB, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS CURMONTH, 
ROUND(DATEDIFF(DATE_FORMAT(NOW() ,'%Y-%m-01'), applicationDoB) / 365.2425,1) AS age, application.applicationID, application.applicationAcknowledged, application.applicationContacted, application.applicationDate
FROM application
ORDER BY application.applicationDate ASC