CODEWITHSUNDEEP
mysqlsql
Mukhila Asokan
Mukhila Asokan

Reputation: 643

MYSQL Query Age Calculation

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.

Output

Upvotes: 0

Views: 209

Answers (3)

Akina
Akina

Reputation: 42622

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

Upvotes: 1

ASHVINI KUMAR
ASHVINI KUMAR

Reputation: 78

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

Upvotes: 0

Abdul Qayyum Shah
Abdul Qayyum Shah

Reputation: 39

Try this one it will work,

Query,

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

Upvotes: 0

Related Questions