Get between ages from date of birth in sql

Question

I have a scenario in which I have to get those users whose age is between say (10 - 20) in sql, I have a column with date of birth dob. By executing the below query I get the age of all users.

SELECT 
    FLOOR((CAST (GetDate() AS INTEGER) - CAST(dob AS INTEGER)) / 365.25) AS Age     
FROM users

My question is how should I write my query so that I can get that users whose age is between 10 to 20

"By executing the below query I get the age of all users." -- No, you don't. It will not be correct all of the time, even if you don't have any counterexample in your current data. — , Mar 19 '16 at 10:51
I agree, but I want a different query here... I dont have the age column, I only have the dob(date of birth) column — Furquan Khan, Mar 19 '16 at 10:52
Also, doesn't `GetDate()` return a date object rather than number of days? How are storing your `dob` column? — Tim Biegeleisen, Mar 19 '16 at 10:52
@FurquanKhan Even so, there's no reason not to calculate the age correctly. — , Mar 19 '16 at 10:54

score 4 · Accepted Answer · answered Mar 19 '16 at 11:01

4

I don't have a SQL-Server available to test right now. I'd try something like:

select * from users where datediff(year, dob, getdate()) between 10 and 20;

answered Mar 19 '16 at 11:01

mauro

5,730
2
26
25

Btw, `DATEDIFF` doesn't do the same calculation like a human being, it's simply YEAR(getdate) -YEAR(dob)` – dnoeth Mar 19 '16 at 11:49

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

First add computed field Age like you already did. Then make where filtering on the data.

SELECT * FROM
   (SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(dob AS INTEGER)) / 365.25) AS Age, * 
   from users) as users
WHERE Age >= 10 AND Age < 20

There are number of ways to calculate age.

edited May 23 '17 at 12:23

Community

1
1

answered Mar 19 '16 at 10:55

Ivan Gritsenko

4,166
2
20
34

It is throwing error `Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'where'. ` – Furquan Khan Mar 19 '16 at 10:56

score 1 · Answer 3 · edited Jun 26 '18 at 16:06

1

You can try the following:

SELECT * FROM TableName 
WHERE DATEDIFF(year, dob, getdate()) between @dob and @currentDate;

OR

SELECT DATEDIFF(day,'2014-06-05','2014-08-05') AS DiffDate;

Reference: http://www.w3schools.com/sql/func_datediff.asp OR

http://sqlhints.com/2015/07/10/how-to-get-difference-between-two-dates-in-years-months-and-days-in-sql-server/

edited Jun 26 '18 at 16:06

RubioRic

2,442
4
28
35

answered Mar 19 '16 at 11:06

Dev

410
5
23

Doesn't datediff return integer? – mauro Mar 19 '16 at 11:08
Yes iut does return integer, check this link http://www.w3schools.com/sql/func_datediff.asp – Dev Mar 19 '16 at 11:10

score 0 · Answer 4 · edited Mar 19 '16 at 11:15

0

Below query should return the users :

SELECT *  
from users 
where FLOOR ( (CAST (GetDate() AS INTEGER) - CAST(dob AS INTEGER)) / 365.25) 
between 10 and 20;

edited Mar 19 '16 at 11:15

Thomas G

9,886
7
28
41

answered Mar 19 '16 at 11:11

amyst

616
1
10
22

score 0 · Answer 5 · answered Mar 19 '16 at 11:54

Instead of calculating each users age you might simply change your WHERE-condition to:

where dob between cast(dateadd(year, 20, getdate()) as date)
              and cast(dateadd(year, 10, getdate()) as date)

Now it's a sargable expression and might use an index.

score 0 · Answer 6 · edited May 23 '17 at 11:59

I found this question trying to figure out something very similar. I'm really new to SQL and found a discussion about calculating age from date of birth here.

I've adapted one of the answers there into this: I think it works.

SELECT
    (0 + Convert(Char(8),GETDATE(),112) - Convert(Char(8),dob,112)) / 10000 AS Age
FROM
users
WHERE
    (0 + Convert(Char(8),GETDATE(),112) - Convert(Char(8),dob,112)) / 10000 BETWEEN 10 and 20
;

Get between ages from date of birth in sql

6 Answers6