MySQL Date Issue - how to compute?

Question

I have a MySQL database with a date_of_birth field. The data stored in this field is of the format: MM-DD-YYYY. I need to compute age for this field so I must compare and take the difference of CurDate().

To do this I have written the following:

select FLOOR((DATE_FORMAT(curdate(), '%m-%d-%Y') - date_of_birth)/10000) 
from patients

So I would expect it to be comparing the CurDate() of MM-DD-YYYY minus DOB of MM-DD-YYYY. However, for my one test case it continues to return a -1. I've got this working fine with MSSQL but it seems MySQL is a bit more picky/less user friendly.

Am I missing something here? Whats the deal, please help!

Thanks

Eh, why aren't you using mysql date format? Storing it in native is just... Inefficient. Is it possible to change your table structure? — Vyktor
– Vyktor, Commented Feb 9, 2012 at 19:53
It's not my design Vyk. It was the idiots before me that knew nothing about MySQL. So I'm stuck with it but I got it working now. — Encryption
– Encryption, Commented Feb 9, 2012 at 20:07

Cheery · Accepted Answer · 2012-02-09 19:59:20Z

4

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - 
      (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')) 
       AS age 
FROM patients

http://ma.tt/2003/12/calculate-age-in-mysql/

ps: if your date is in another format (but I would advise you to keep it 'native' format YYYY-MM-DD) then

SELECT 
 DATE_FORMAT(NOW(), '%Y') - 
              DATE_FORMAT(STR_TO_DATE(date_of_birth, '%m-%d-%Y'), '%Y')
- (DATE_FORMAT(NOW(), '00-%m-%d') <
              DATE_FORMAT(STR_TO_DATE(date_of_birth, '%m-%d-%Y'), '00-%m-%d')) 
   AS age 
FROM patients

edited Feb 9, 2012 at 19:59

answered Feb 9, 2012 at 19:51

Cheery

16.3k45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Encryption Over a year ago

Still just returning NULLs. My date_of_birth is stored in format MM-DD-YYYY - this is the stumbling block.

Encryption Over a year ago

Thanks Cheery! Was attempting to make those same modifictations.. As for it being stored in an unconventional format for MySQL, well that cannot be helped at this point :( Gotta deal with it.

Cheery Over a year ago

@Encryption You can easily update all the data in DB to the specified format with the couple of requests (including altering of the table's structure). The best way is to format date at output.

Encryption Over a year ago

I realize that but this is connected to a web interface. Would require interface changes as to how that information is displayed and saved. Not something I'm going to deal with right now(all I need was a report, heh).

Brian Mains · Accepted Answer · 2012-02-10 03:29:58Z

2

You can use datediff()

SELECT DATEDIFF(CURDATE(),date_of_birth) AS Age

edited Feb 10, 2012 at 3:29

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

answered Feb 9, 2012 at 19:47

Brian

2,25918 silver badges24 bronze badges

3 Comments

Encryption Over a year ago

Doesn't work. Just returns NULLs. Even tried using DATEDIFF(DATE_FORMAT(curdate(), '%m-%d-%Y'), date_of_birth)

Cheery Over a year ago

DATEDIFF returns difference in days

Brian Over a year ago

do the date_format on date_of_birth and see if that works. I know with oracle, even fields stored as dates can be quirky and I had to cast it as a date anyway. Try masking both dates as well so that the db is comparing apples to apples.

Collectives™ on Stack Overflow

MySQL Date Issue - how to compute?

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related