Get birthday with a query

Question

I have problems to get age of certain dates.

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

The result is:

| 27-07-1955 | NULL |

What's wrong in this select?

score 0 · Answer 1 · edited Mar 31 '18 at 19:51

0

Would this work?

SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM users;

edited Mar 31 '18 at 19:51

Evert

answered Mar 31 '18 at 19:47

Roemer

no. all results are as NULL – Guif If Mar 31 '18 at 19:58
Then probably birthday is all NULL, or you have a wrong data type. It works perfectly fine on a normal datefield that is not null. I tested. – Roemer Mar 31 '18 at 20:11
I guess from your original posting you use a string. Which is not the right way to store dates, we have the DATE type for that. In that case you should use `SELECT TIMESTAMPDIFF(YEAR, str_to_date(birthday, '%d-%m-%Y'), CURDATE()) AS age FROM users;` But I highly recommend you use the right datatype. Also, the answer you accepted als a correct answer will give wrong answers. For people over 50 sometimes really wrong. – Roemer Mar 31 '18 at 20:21

Vahid Moghadam · Accepted Answer · 2018-03-31T19:43:45.443

-1

You can use this query, it better:

SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.birthday, '%d-%m-%Y'))/365 AS ageInYears
  FROM YOUR_TABLE t

edited Mar 31 '18 at 19:43

answered Mar 31 '18 at 19:40

Vahid Moghadam

Nope, that gives a lot of incoorect answers: Born on 31 dec, andthe next day you're already 1 year old this way. – Roemer Mar 31 '18 at 19:42
O, i changed it ;) – Vahid Moghadam Mar 31 '18 at 19:44
Doesn't account for leap years, also goves wrong results. – Roemer Mar 31 '18 at 20:08
right! I add the parameter ROUND too – Guif If Mar 31 '18 at 20:08
1

Leap years are about more than rounding. You will get wrong results. – Roemer Mar 31 '18 at 20:14
I change the table to a data type. thanks! – Guif If Apr 01 '18 at 17:00

2 Answers2