CODEWITHSUNDEEP
sqlsqlitedate
InterLinked
InterLinked

Reputation: 1403

How to calculate approximate age based on birth year?

There are several questions on SO about calculating age based on DOB. However, I have several tables where only BirthYear is provided. There is no month or day. I would like to simply take the current year and subtract the value in BirthYear from that to calculate an approximate age. As long as it is within a year or so, the accuracy doesn't matter to me as much.

I don't want to modify the table in any way. I just wanted an additional column with calculated age showing when I run the query. I keep seeing answers with ALTER TABLE or UPDATE statements but I don't think that would be the way to go here...

Currently I have a statement to order by birth year as well:

ORDER BY CAST(replace(replace(BirthYear,',',''),'$','') AS REAL) ASC

Upvotes: 0

Views: 1164

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

In SQLite, you can get the current year as:

select strftime('%Y', date('now'))

I don't know what BirthYear looks like, but you can do:

select (strftime('%Y', date('now')) - BirthYear) as age

Or whatever expression gets the birth year.

Upvotes: 2

CoolBots
CoolBots

Reputation: 4889

You can use DATEDIFF function to achieve this result; instead of ALTERing the existing table, a VIEW can be created that includes a calculated ApproximateAgecolumn:

CREATE VIEW vw_MyTableWithAge
AS

SELECT  *
,       DATEDIFF(year, DOB, GETDATE()) AS ApproximateAge
FROM    MyTable

year is an input to the function, literally (no quotes). DOB is the column name in your table that stores the date of birth.

More info on DATEDIFF: https://www.w3schools.com/sql/func_sqlserver_datediff.asp

EDIT: I just saw this is SQLite, note MSSQL; here's the equivalent SQLite code:

CREATE VIEW vw_MyTableWithAge
AS

SELECT  *
,       strftime('%Y', date('now')) - DOB AS ApproximateAge
FROM    MyTable

strftime syntax borrowed from @Gordon Linoff's answer. More info on this function: https://www.techonthenet.com/sqlite/functions/strftime.php

Once the view is created, you can use it in place of your table. You can still order by the calculated age; the column is now treated as any other column would be in a table, but the original table is not modified in any way:

SELECT *
FROM   vw_MyTableWithAge
ORDER BY ApproximateAge ASC

Upvotes: 1

Related Questions