Calculate Age in SQLite | SQL Bits | Kovolff

Ages of people or entities are never input in a database. Why? Because ages are continually changing.

So How do we calculate ages?
The simplest way to approach this problem is to subtract today’s year from the birth year.

In SQLite you get today’s date using the function date() with the keyword now, i.e. date(‘now’)

To get just the year, month or day of today you must use the function strftime to get the desired component.
i.e.
strftime(‘%Y’, date(‘now’)) gets you the year of now
strftime(‘%m’, date(‘now’)) produces the month of now
strftime(‘%d’, date(‘now’)) returns the day of now

So now we are in a position to carry out a simple age calculation
strftime(‘%Y’, date(‘now’)) – strftime(‘%Y’, date(birthdate))

In other words take now’s year and subtract that from the birth year.

However the above calculation is not very accurate. It does not cater for the situation when a person’s birthday is after today. So if we have April and a person is born in September, then this person has not aged yet.

This person’s age would not be now’s year – birth year but now’s year – birth year – 1

This problem can be solved by using conditions
i.e.
CASE
WHEN today’s month greater than birth month THEN now’s year – birth year
WHEN today’s month smaller than birth month THEN now’s year – birth year – 1
END

That covers all birth dates either before today’s month or after. But what about those birth dates occuring in the current month?

For this we have to add a further condition
i.e.
CASE
WHEN today’s month greater than birth month THEN now’s year – birth year
WHEN today’s month equals birth month THEN
CASE
WHEN today’s day greater or equal to birth day THEN now’s year – birth year
ELSE now’s year – birth year – 1
WHEN today’s month smaller than birth month THEN now’s year – birth year – 1
END

So this is the algorithm for calculating ages in SQLite. All you have to do right now is convert the above into SQL code.

#age #calculate #sqlite #database #automatic