Question 331548 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL compare 2 digit year with today's date

I have DOB stored in dB as 2 digit value (IE: 98,99,05…), how I can compare it to today’s date to get age?

Tried different variations, even converted today’s date to 2 digit but I cannot compare it…

TIMESTAMPDIFF(year, d.dob, DATE_FORMAT(CURDATE(), '%y')) AS age

Thanks in advance!

Tags:

3

Answers


  1. 0

    You can use something like next query:

    select
    year(current_date) - (yob + 1900),
    if(
        year(current_date) - (yob + 1900) > 100, 
        year(current_date) - (yob + 2000),
        year(current_date) - (yob + 1900)
    ) age
from t

    just decide what the max age allowed.
    Play with query here

    Login or Signup to reply.
  2. 0

    As you told that dob is ranges from 1970 to 2010, and is stored as a DATE type column, you can compare it with today’s date to calculate the age by adjusting the year component of each date. Here’s how you can do it:

    SELECT TIMESTAMPDIFF(YEAR, 
                     CASE WHEN YEAR(STR_TO_DATE(dob, '%y')) > 10 THEN STR_TO_DATE(CONCAT('19', dob), '%Y')
                          ELSE STR_TO_DATE(CONCAT('20', dob), '%Y')
                     END,
                     CURDATE()) AS age
FROM your_table;
    Login or Signup to reply.
  3. 0

    Assuming the oldest supported birth year is 1970, you can approximate age best by assuming the person was born in the middle of the year and doing some simple math:

    timestampdiff(year, case when d.dob >= 70 then '1900-07-01' else '2000-07-01' end + interval d.dob year, curdate())

    But without birth month and day, you cannot calculate exact age.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search