SQLite: How to calculate age from birth date

Question

What is the best way to calculate the age in years from a birth date in sqlite3?

Romans

Answer 1

You can convert the dates to floating point numbers and subtract…

cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', dob) as int)

…where dob is a valid SQLite time string.

Hurter

Answer 2

SELECT (strftime('%Y', 'now') - strftime('%Y', Birth_Date)) - (strftime('%m-%d', 'now') < strftime('%m-%d', Birth_Date));

This one works.
I merely ported this MySQL example: http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

Romans

Answer 3

Just to clarify kai's answer (it seems that editing is now very much unwelcome, and comments have a strong size limitation and formatting issues):

SELECT (strftime('%Y', 'now') - strftime('%Y', Birth_Date)) 
     - (strftime('%m-%d', 'now') < strftime('%m-%d', Birth_Date) );

Let's assume we want full years: for the first calendar year of life, this would be zero.

For the second calendar year, this would depend on the date -- i.e. it would be:

• 1 for strftime('%m-%d', 'now') >= strftime('%m-%d', Birth_Date) ["case A"]
• and 0 otherwise ["case B"] ;

( I assume sqlite does lexicographic comparison of two date strings and returns an integer value. )

For any other calendar year, it would be the number of years between the current and the second -- i.e. strftime('%Y', 'now') - strftime('%Y', Birth_Date) - 1, plus the same as above.

In other terms, we subtract 1 from strftime('%Y', 'now') - strftime('%Y', Birth_Date) only if the opposite of "case A" happens -- that is, if and only if strftime('%m-%d', 'now') < strftime('%m-%d', Birth_Date), hence the formula.

Pekingese

Answer 4

I have spent whole my Weekend on this Topic and created this Query.

SELECT ID,
       Name,
       InitialDate,
       LastDate,
       CASE 
           WHEN strftime('%m', LastDate) > strftime('%m', InitialDate) THEN strftime('%Y', LastDate) - strftime('%Y', InitialDate) 
           WHEN strftime('%m', LastDate) < strftime('%m', InitialDate) THEN strftime('%Y', LastDate) - strftime('%Y', InitialDate) - 1 
           WHEN strftime('%m', LastDate) = strftime('%m', InitialDate) THEN 
           CASE 
               WHEN strftime('%d', LastDate) >= strftime('%d', InitialDate) THEN strftime('%Y', LastDate) - strftime('%Y', InitialDate) 
               ELSE strftime('%Y', LastDate) - strftime('%Y', InitialDate) - 1 
           END 
       END AS Years,
       CASE
           WHEN strftime('%m', LastDate) == strftime('%m', InitialDate) THEN
               CASE
                   WHEN strftime('%d', LastDate) == strftime('%d', InitialDate)    THEN ( strftime('%m', LastDate) - strftime('%m', InitialDate) ) -- i.e., 0
                   WHEN strftime('%d', LastDate) > strftime('%d', InitialDate)     THEN ( strftime('%m', LastDate) - strftime('%m', InitialDate) ) -- i.e., 12
                   WHEN strftime('%d', InitialDate) > strftime('%d', LastDate)     THEN (strftime('%m', LastDate) - strftime('%m', InitialDate)) + 11 -- i.e., 11
               END
           WHEN strftime('%m', LastDate) > strftime('%m', InitialDate) THEN 
               CASE
                   WHEN strftime('%d', LastDate) == strftime('%d', InitialDate)    THEN strftime('%m', LastDate) - strftime('%m', InitialDate)
                   WHEN strftime('%d', LastDate) > strftime('%d', InitialDate)     THEN strftime('%m', LastDate) - strftime('%m', InitialDate)
                   WHEN strftime('%d', InitialDate) > strftime('%d', LastDate)     THEN ( strftime('%m', LastDate) - strftime('%m', InitialDate) ) - 1
               END
           WHEN strftime('%m', InitialDate) > strftime('%m', LastDate) THEN
               CASE 
                   WHEN strftime('%d', LastDate) == strftime('%d', InitialDate)    THEN 12 - ( strftime('%m', InitialDate) - strftime('%m', LastDate) )
                   WHEN strftime('%d', LastDate) > strftime('%d', InitialDate)     THEN 12 - ( strftime('%m', InitialDate) - strftime('%m', LastDate) )
                   WHEN strftime('%d', InitialDate) > strftime('%d', LastDate)     THEN 11 - (strftime('%m', InitialDate) - strftime('%m', LastDate))
               END           
       END AS Months,
       CASE 
           WHEN strftime('%d', LastDate) == strftime('%d', InitialDate)    THEN 0
           WHEN strftime('%d', LastDate) > strftime('%d', InitialDate)     THEN strftime('%d', LastDate) - strftime('%d', InitialDate)
           WHEN strftime('%d', InitialDate) > strftime('%d', LastDate)     THEN ( strftime('%d', date(InitialDate, 'start of month', '+1 month', '-1 day') ) - strftime('%d', InitialDate) + strftime('%d', LastDate) )
            
   END AS Days
  FROM tbl_Dates;

Tested on a Data Set consist of 248 Records. Years and Months are with calculated 100% Accuracy. While Days' Calculation is having issues with Difference of ±1 Day (124 out of 248 Records). I would request other Genius Minds to Dig out and find the remaining solution. For me Its OK and working 100% Fine.

Antimagnetic

Answer 5

I found this that maybe would help you:

select
    case
        when date(dob, '+' ||
            strftime('%Y', 'now') - strftime('%Y', dob) ||
            ' years') >= date('now')
        then strftime('%Y', 'now') - strftime('%Y', dob)
        else strftime('%Y', 'now') - strftime('%Y', dob) - 1
    end
    as age
from t;

From http://www.mail-archive.com/[email protected]/msg20525.html

Nomination

Answer 6

I had to solve a similar problem for Microsoft SQL Server which doesn’t have a proper age function.

For many applications, you want the number of complete years from the date of birth. For example, legal drinking or driving age. In these cases you want to make sure that if the birthday is today or before today, the year counts, but not if the birthday is tomorrow.

In SQLite, I have the formula:

date(
    today,
    '-'||(strftime('%m',dob)-1)||' months',
    '-'||(strftime('%d',dob)-1)||' days'
) - dob AS age;

where

• today is the asking date
• dob is the date of birth

assuming the date is a string in ISO8601 format (yyyy-mm-dd).

The logic is:

• date1 - date2 will only find the difference in years
• This would be OK if the year started on the birthday, but it probably doesn’t
• Adjust the asking date by the difference between the birthday and the start of the year.

The extra -1 is to compensate for the fact that months and days start on 1, not 0.

You can test it with the following:

WITH dates AS (
    SELECT
        '2022-02-23' AS today,
        '1943-02-24' AS dob
)
SELECT
    date(
        today,
        '-'||(strftime('%m',dob)-1)||' months',
        '-'||(strftime('%d',dob)-1)||' days'
    )-dob AS age
FROM dates;

fiddling with the today value to find George Harrison’s age.

Sendoff

Answer 7

To get @Bruno Costa's answer to work I had to add brackets around the strftime difference calculation on the 4th line and reverse the comparison to 'now' to be less than or equal: as I understand it the calculation is working out whether the calculated date is before or after today. If the calculated date is today or earlier then the birthday has already been this year or is today:

select
    case
        when date(dob, '+' ||
            (strftime('%Y', 'now') - strftime('%Y', dob)) ||
            ' years') <= date('now')
        then strftime('%Y', 'now') - strftime('%Y', dob)
        else strftime('%Y', 'now') - strftime('%Y', dob) - 1
    end
    as age
from t;

Note that the solution linked to in @Bruno Costa's answer was untested.

Lacunar

Answer 8

Another solution is to use the day of the year %j as a fraction of the year by dividing it by 365.0. So you end up with:

select strftime('%Y', 'now') + strftime('%j', 'now') / 365.2422) - (strftime('%Y', Birth_Date) + strftime('%j', Birth_Date) / 365.2422);

You can then cast the result to an integer:

select CAST(strftime('%Y', 'now') + strftime('%j', 'now') / 365.2422) - (strftime('%Y', Birth_Date) + strftime('%j', Birth_Date) / 365.2422) AS INT);

or use ROUND() to round the outcome:

select round(strftime('%Y', 'now') + strftime('%j', 'now') / 365.2422) - (strftime('%Y', Birth_Date) + strftime('%j', Birth_Date) / 365.2422));

I have updated the calculation so it uses the correct decimal number of days in a solar year as noted by Robert in the comment below.

The difference with the other calculations is that the result of this calculation is a decimal number that can be cast to an integer (to get the exact amount of years) or rounded (to get the approximate amount of years).

For birthdays the approximate amount of years may not be that relevant, but for the age of something you bought it makes more sense.

Octopus

This is not accurate. You can improve it slightly by using 365.2422 instead, but it won't conform to cultural notions of age. – Threlkeld

Answer 9

I recently needed to do this. I was able to accomplish the task by writing:

strftime('%Y %m %d',date('now')) - strftime('%Y %m %d',emp.HireDate)

where emp.HireDate is the HireDate in Employees table of chinook database.

Electromotor

Answer 10

IT is very dificult to do in sqllite... so better you retrive the birth date from the database nd then add this logic

private Calendar mConvert_Date_To_Calendar(String dateToConvert)
{
    // TODO Auto-generated method stub
    try
    {
        Calendar calConversion = Calendar.getInstance();
        Date date1 = null;
        try
        {
            date1 = new SimpleDateFormat("dd/MM/yy").parse(dateToConvert);
        }
        catch (ParseException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        calConversion.setTime(date1);
        System.out.println("mConvert_Date_To_Calender date1: " + date1
                + "caleder converted done:" + calConversion.getTime());
        return calConversion;
    }
    catch (Exception e)
    {
        e.printStackTrace();
        Log.i(TAG, "" + e);
        return null;
    }
}


private Calendar myCurrentCalender()
{
    try
    {
        Calendar myCurrentCal = Calendar.getInstance();
        myCurrentCal.set(myCurrentCal.get(Calendar.YEAR), myCurrentCal.get(Calendar.MONTH),
                myCurrentCal.get(Calendar.DAY_OF_MONTH));
        return myCurrentCal;
    }
    catch (Exception e)
    {
        e.printStackTrace();
        Log.e(TAG, "Unable to get current calendar!");
        return null;
    }
}

public int daysBetween(Date d1, Date d2)
{
    System.out.println("calculated age :"
            + (((d2.getTime() - d1.getTime()) / ((24 * 1000 * 60 * 60)) / 365)));

    return (int) (((d2.getTime() - d1.getTime()) / (24 * 1000 * 60 * 60)) / 365);
}

The first function will calculate the birth date in calendar format, second will calculate current date, the third will find the difference between the two. These functions shud be called as shown below

dateOfBirth = mConvert_Date_To_Calendar(age);

currentDate = myCurrentCalender();

candidateAge = daysBetween(dateOfBirth.getTime(), currentDate.getTime());

"candidateAge" will have the age.

Bio

Calendar dateOfBirth; Calendar currentDate; int candidateAge; these are the datatype of those variables – Bio