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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

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/[e-mail]/msg20525.html

Answer 6

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.