-1

I'm trying to get the difference in years with MySQL but it is showing an error when selecting the same.

SELECT(
    DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT('birthdate', '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT('birthdate', '00-%m-%d')) AS age
) FROM register where 'reg_id' = user254

What is the error?

Bojangles
  • 99,427
  • 50
  • 170
  • 208
Kundan Raj
  • 77
  • 1
  • 6
  • 1
    I'll ask you this. What's the error generated? What do you exactly want to do? – John Woo Aug 21 '12 at 08:37
  • It's very nice of you to tell us what the error is. How is anyone supposed to help you when you give barely any information about your problem? – Bojangles Aug 21 '12 at 08:38

3 Answers3

3

Try with this:

SELECT (YEAR(NOW()) - YEAR(birthdate)) AS age) 
FROM register 
WHERE reg_id = 'user254'

Or (as suggested here)

SELECT (YEAR(NOW()) - YEAR(birthdate) - 
        (DATE_FORMAT(NOW(), '%m%d') < DATE_FORMAT(birthdate, '%m%d'))) AS age 
FROM register
WHERE reg_id = 'user254'
Community
  • 1
  • 1
Marco
  • 56,740
  • 14
  • 129
  • 152
3

Just substracting the years does not actually give you the age - it will be as if everyone was born on january 1st. Try just doing a datediff:

SELECT FLOOR((DATEDIFF(NOW(),birthdate))/365)
FROM register 
WHERE reg_id = 'user254'

Remember: put quotes around literal values (like 'user254' or 'John'), but no regular quotes around table names or column names! You will want to use backtikcs (`) for those.

Konerak
  • 39,272
  • 12
  • 98
  • 118
0

Try this:

SELECT ( YEAR(NOW()) - YEAR(birthdate) - 
(DATE_FORMAT(NOW(), '%m%d') < DATE_FORMAT(birthdate, '%m%d'))
AS age) FROM register WHERE 'reg_id' = user254
Amamaru Hakan
  • 76
  • 3