Get difference in years between two dates in MySQL as an integer

Question

I am trying to calculate how old is a person in a database.
Let's suppose to have this simple table:

student(id, birth_date);

Where id is the primary key (truly the table is more complex but I have simplified it).
I want to get how much old is a single person:

select id, datediff(curdate(),birth_date) 
from student

But it returns a result in days, and not in years.I could divide it by 365:

select id, datediff(curdate(),birth_date) / 365
from student

But it returns a floating point value, and I want an integer.
So I could calculate the years:

select id, year(curdate())-year(birth_date) 
from student

But there is a problem: for instance now is May, if a person war born in June, 1970 he has still 31 years and not 32, but the expression returns 32.
I can't come out of this problem, can someone help me?

score 108 · Accepted Answer · edited Dec 24 '22 at 22:00

108

For anyone who comes across this:

another way this can be done is:

SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS difference FROM student

For differences in months, replace YEAR with MONTH, and for days replace YEAR with DAY

edited Dec 24 '22 at 22:00

starball

20,030
7
43
238

answered May 23 '14 at 08:59

Rat-a-tat-a-tat Ratatouille

7,109
5
31
41

1

Here is the official doc of `TIMESTAMPDIFF`: https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_timestampdiff – TonyLxc Feb 09 '18 at 20:30

score 18 · Answer 2 · answered May 08 '12 at 21:28

18

select id, floor(datediff(curdate(),birth_date) / 365)
from student

What about flooring the result to be an integer?

answered May 08 '12 at 21:28

Scott Bonner

2,890
5
27
28

11

This does not take leap years into account. The drift can get significant. `select floor(datediff("2017-03-19", "1975-03-30") / 365);` says 42. That person is 41, 11 days off. – Schwern Jan 30 '17 at 20:47
2

Agreeing with @Schwern – shrimpwagon Aug 18 '17 at 16:08
Dont use this answer. – Leandro Bardelli Jan 24 '19 at 20:23
1

Agreed, don't do this - if nothing else, should it span enough years, you won't be accounting for leap years, thus you'll be a day out for every 4 years spanned. – matt.chatterley Jun 18 '19 at 10:32
How can we manage 365 and 366 days (leap years) in suggested above query? – Kamlesh Jul 24 '19 at 12:59
@kamelsh, you can divide by 365.25 because leap years occur once out of every 4 years. – urig May 10 '23 at 08:22

score 4 · Answer 3 · answered May 08 '12 at 21:29

4

Try:

SELECT 
  DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birth_date)), '%Y')+0
  AS age FROM student;

answered May 08 '12 at 21:29

elias

15,010
4
40
65

1

This can be simplified as `SELECT YEAR(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS("1975-04-20")));` And it correctly deals with leap years. – Schwern Jan 30 '17 at 21:05
Does it correctly deal with leap years though? Information regarding the actual dates are getting lost in the TO_DAYS function, and I don't see how the FROM_DAYS will possibly recover and account for the exact leap years @elias – Madhuchhanda Mandal Oct 26 '21 at 11:23

score 4 · Answer 4 · answered Dec 21 '12 at 07:53

4

The accepted answer is almost correct, but can lead to wrong results.

In fact, / 365 doesn't take into consideration leap years, and can lead to falsy results if you compare a date that has the same day&month than the birthdate.

In order to be more accurate, you have to divide it by the average days in years for 4 years, aka (365 * 4) + 1 (the leap year every 4 years) => 365.25

And you will be more accurate :

select id, floor(datediff(curdate(),birth_date) / 365.25) from student

Tested for one of my project and it's working.

answered Dec 21 '12 at 07:53

Cyril N.

38,875
36
142
243

Isn't datediff(curdate(),birth_date casted to a floating point? – Ramy Al Zuhouri Dec 21 '12 at 12:34
I couldn't say for sure, but I tested it, and `/365` returned a different (false) result than ` / 365.25` indicated here. – Cyril N. Dec 21 '12 at 13:31
Yeah but shouldn't I cut the result with a function like floor? I could get a non-integer age. – Ramy Al Zuhouri Dec 21 '12 at 13:45
1

I'm not sure to understand here. floor returns a BigInt, not a floating value. datediff returns an int, divised by a float, but rounded to the lower int. – Cyril N. Dec 21 '12 at 13:50
This is still not quite right. Leap years are not every 4 years, they're every 4 years, except every 100, except every 1000. 1900 was not a leap year. 2000 was. This will ***happen to work*** because 2000 was a leap year and most birthday calculations will span 2000, but it be off by a day when spanning 1900 or 2100. `select floor(datediff("2117-03-19", "2075-03-19") / 365.25)` says 41, it should be 42. You can't divide the number of days to get years, even using 365.242222 won't work quite right. You have to do the math. – Schwern Jan 30 '17 at 21:00

score 1 · Answer 5 · answered May 08 '12 at 21:28

1

Why not use MySQL's FLOOR() function on the output from your second approach? Any fractions will be dropped, giving you the result you want.

answered May 08 '12 at 21:28

Harper Shelby

16,475
2
44
51

score 1 · Answer 6 · answered Apr 01 '18 at 17:20

I have not enough reputation to add comment to an answer by Rat-a-tat-a-tat Ratatouille to improve his code. Here is the better SQL-query:

SELECT IFNULL(TIMESTAMPDIFF(YEAR, birthdate, CURDATE()), YEAR(CURDATE()) - YEAR(birthdate)) AS age

This is better because sometimes "birthdate" may contain only year of birth, while day and month is 0. If TIMESTAMPDIFF() returns NULL, we can find rough age by subtracting the current year from the year of birth.

Amit · Answer 7 · 2012-05-08T21:58:30.460

0

You can use this to get integer value.

select id, CAST(datediff(curdate(),birth_date) / 365 as int)
from student

If you want to read about CONVERT() and CAST() here is the link.

edited May 08 '12 at 21:58

answered May 08 '12 at 21:41

Amit

21,570
27
74
94

score 0 · Answer 8 · answered Mar 26 '13 at 21:19

0

I'd suggest this:

DATE_FORMAT(NOW(),"%Y")
   -DATE_FORMAT(BirthDate,'%Y')
   -(
     IF(
      DATE_FORMAT(NOW(),"%m-%d") < DATE_FORMAT(BrthDate,'%m-%d'),
      1,
      0
     )
    ) as Age

This should work with leap-years very well ;-)

answered Mar 26 '13 at 21:19

Marek Demčák

31
1

score 0 · Answer 9 · edited Dec 08 '16 at 20:53

0

This works, even taking in account leap years!

select floor((cast(date_format('2016-02-29','%Y%m%d') as int) - cast(date_format('1966-03-01','%Y%m%d') as int)/10000);

Just be sure to keep the floor() as the decimal is not needed

edited Dec 08 '16 at 20:53

Jarod Moser

7,022
2
24
52

answered Dec 08 '16 at 18:33

Steve Burke

1

Get difference in years between two dates in MySQL as an integer

9 Answers9

Linked