MYSQL Query Age Calculation-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Try this one it will work,

Query,

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

CodePudding user response：

Two digits years are problematic. MySQL docs.

As 1- or 2-digit strings in the range '0' to '99'. MySQL converts values in the ranges '0' to '69' and '70' to '99' to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.

As 1- or 2-digit numbers in the range 0 to 99. MySQL converts values in the ranges 1 to 69 and 70 to 99 to YEAR values in the ranges 2001 to 2069 and 1970 to 1999.

SELECT str_to_date('21-Dec-69', '%d-%M-%y')
> 2069-12-21

SELECT str_to_date('21-Dec-70', '%d-%M-%y')
> 1970-12-21

CodePudding user response：

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