Date Difference in mysql

Question

hey all,

i have a problem regarding date diff function of mysql, i can use it and it is simple but i dont understand that how do i use it to collect difference within the table field. e.g. i have a column "dob" and i want to write a query that will do something like

select dateDiff(current_timeStamp,dob) from sometable 'here dob is the table column

i mean i want difference from current date time to the table field "dob", each query result brings its difference is the age of the user.

i hope i am clear :(

but i dont think so since i cant understand my own question but still please if somebody understood then answer me

thanks

Answer 1

You mean like this?

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), dob)), "%Y")+0 AS age from sometable

(Source)

Answer 2

If you want, for each user, display the age in years, do

select name,extract(year from (from_days(dateDiff(current_timestamp,dob)))) 
       from sometable;

Answer 3

If I understand your comments on the previous answers, the date-of-birth column is not actually a DATE value but a string in the format m/d/y. I strongly recommend you change this; it slows down any date computations you want to do and you risk invalid date values getting entered into the column.

I think this is what you need. It uses the STR_TO_DATE() function and an algorithm for computing the age from the MySQL documentation:

SELECT YEAR(CURDATE()) - YEAR(STR_TO_DATE(dob, '%m/%d/%Y'))
- (RIGHT(CURDATE(), 5) < RIGHT(STR_TO_DATE(dob, '%m/%d/%Y'), 5)) AS age
FROM sometable;