Tutorial :SQLite: How to calculate age from birth date


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


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


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.


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.


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/sqlite-users@sqlite.org/msg20525.html


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.


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.


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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »