CODEWITHSUNDEEP
sql
Furquan Khan
Furquan Khan

Reputation: 1594

Get between ages from date of birth in sql

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

Upvotes: 1

Views: 9604

Answers (6)

Dev
Dev

Reputation: 410

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/

Upvotes: 1

Neil Scrivener
Neil Scrivener

Reputation: 15

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
;

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

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.

Upvotes: 0

amyst
amyst

Reputation: 616

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;

Upvotes: 0

mauro
mauro

Reputation: 5940

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;

Upvotes: 4

Ivan Gritsenko
Ivan Gritsenko

Reputation: 4236

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.

Upvotes: 1

Related Questions