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

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 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

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.