Calculating age at time of an event in SQL

Question

What is the best way to calculate age based on an event? I'm trying to look for individuals based on their age when an event took place? I'm relative new to SQL and the dates functions, I can't wrap my head around.

Example:

ID        Name         Age   DOB          AdmitDate
1234      John Smith   61    1960-07-15   2019-10-22

How can I can calculate whose age was 59 at the time of admit date?

For accuracy, I recommend this https://stackoverflow.com/a/1572938/9947159 — Radagast, Mar 09 '20 at 17:13
SELECT DATEDIFF(DAY, [DOB], AdmitDate) / 365 You can use this as a derived column, put into a temp table, and from that temp table, add your WHERE clause to find people who are 59. — jw11432, Mar 09 '20 at 18:09

score 1 · Answer 1 · edited Mar 09 '20 at 17:14

1

DATEDIFF provides the function to do this.

e.g.

SELECT DATEDIFF ( yy , '1960-07-15' , '2019-10-22' )

edited Mar 09 '20 at 17:14

Eric Brandt

7,886
3
18
35

answered Mar 09 '20 at 17:02

Peter Schofield

939
7
13

1

What does `SELECT DATEDIFF ( yy , '2019-12-31' , '2020-01-01' )` return? – Salman A Mar 09 '20 at 17:19
@SalmanA yes this doesn't work for my issue. – Leonardo Mar 09 '20 at 17:30
@SalmanA Why would you close my question when I clearly don't have an answer to this? – Leonardo Mar 09 '20 at 20:16
@Leonardo see the question I linked to. If your problem is different, edit the question with explanation and I'll re-open. – Salman A Mar 09 '20 at 23:57

score 0 · Answer 2 · answered Mar 09 '20 at 17:01

0

Select * from  yourtable 
where  DATEDIFF( year , CAST(DOB AS datetime), CAST(AdmitDate AS datetime)) >= 59

Try this

answered Mar 09 '20 at 17:01

Vijay

137
13

1

This sort of works. But it will return 59 for ANY date in 1960. – Sean Lange Mar 09 '20 at 17:08
2

It'll return 1 year if the dates are 2019-12-31 and 2020-01-01 (the actual age is just 1 day). – Salman A Mar 09 '20 at 17:18
https://stackoverflow.com/q/57599/3943730 Check this link to calculate accurate date difference – Vijay Mar 10 '20 at 07:09

Calculating age at time of an event in SQL

2 Answers2