MYSQL Query Age Calculation

Question

This is my MySQL Query to return the age from the date of birth

SELECT
    PensionerDOB,
    YEAR( CURDATE() ) AS Year, 
    DATE_FORMAT( STR_TO_DATE( PensionerDOB, '%d-%M-%Y' ), '%Y') AS age,
    
    YEAR( CURDATE() ) - DATE_FORMAT( STR_TO_DATE(`PensionerDOB`, '%d-%M-%Y' ), '%Y' ) AS differenage 

FROM
    `pensionerbasicdata`

The query is executed. But it returns the age difference is in a negative value.

Why are you using `STR_TO_DATE` and `DATE_FORMAT` _together_? You don't need to do that. — Dai, Dec 16 '21 at 06:00
If someone is born in 1945, they are not 2045 years old... Please use more accurate column names. — Dai, Dec 16 '21 at 06:00
I could be wrong here, but can't you reverse the minuend and subtrahend to reverse the sign (if the magnitude is correct)? — user904963, Dec 16 '21 at 06:03
@Dai DOB is in the varchar format, So varchar convert to the date format — Mukhila Asokan, Dec 16 '21 at 06:08
@Asokan Storing dates as strings is _wrong_. Why aren't you storing dates in a `date` column? — Dai, Dec 16 '21 at 06:22

Akina · Accepted Answer · 2021-12-16T06:40:01.967

1

SELECT *,
       TIMESTAMPDIFF(year, STR_TO_DATE(CONCAT(SUBSTRING_INDEX(PensionerDOB, '-', 2), '-19', SUBSTRING_INDEX(PensionerDOB, '-', -1)), '%d-%M-%Y'), CURRENT_DATE) AS age
FROM pensionerbasicdata

The problem with 2-digit year fixed - all years are treated as 19xx.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f356258c99b20d13b0c4e2349b801f18

edited Dec 16 '21 at 06:40

answered Dec 16 '21 at 06:28

Akina

39,301
5
14
25

Fixed? By treating all twenty year olds as hundred and twenty year olds? It is `TIMESTAMPDIFF(year,` by the way, i.e. no quotes around year. – Thorsten Kettner Dec 16 '21 at 06:36
@ThorstenKettner Pensioner of 20 years old? well, the fact that **all** years are treated as 20th centure year is marked as bold, if this is not safe then according CASE (instead of single `'-19'`) must be used. The problem with quoted unit name fixed, thanks. – Akina Dec 16 '21 at 06:39
SELECT `PensionerDOB`, YEAR(CURDATE()) as Year, TIMESTAMPDIFF(YEAR, STR_TO_DATE(CONCAT(SUBSTRING_INDEX(PensionerDOB, '-', 2), '-19', SUBSTRING_INDEX(PensionerDOB, '-', -1)), '%d-%M-%Y'), CURRENT_DATE) AS age FROM pensionerbasicdata – Mukhila Asokan Dec 16 '21 at 06:43
@Asokan Asterisk means that you may/must place needed column names and/or expressions instead. – Akina Dec 16 '21 at 06:44

score 0 · Answer 2 · edited Dec 16 '21 at 06:13

0

Try this one it will work,

Query,

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(),YourDateofBirth)), '%Y')+0 AS Age from AgeCalculationFromDatetime

edited Dec 16 '21 at 06:13

mightyteja

825
1
14
38

answered Dec 16 '21 at 06:02

Abdul Qayyum Shah

29
5

What is the purpose of the `+0` part? – Dai Dec 16 '21 at 06:03
if we want to add same days in the age – Abdul Qayyum Shah Dec 16 '21 at 06:09

score 0 · Answer 3 · answered Dec 16 '21 at 06:25

0

here Mysql is not parsing two-digit years as expected.. Instead of 1945- it's returning 2065,1953- it's returning 2053. Please follow this link to parse the date with two digits years. how-to-use-str-to-date-mysql-to-parse-two-digit-year-correctly

answered Dec 16 '21 at 06:25

ASHVINI KUMAR

78
8

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 16 '21 at 06:58

MYSQL Query Age Calculation

3 Answers3