SQL Select query using current date time to get the users ages?

Question

I want to find age, for example, between 20 to 30 from my table from the current system date.

Below is my table details:

GivenName           DOB
  Raj       1950-06-06 00:00:00.000
  Rahul     1951-01-06 00:00:00.000
  Mohan     1952-11-09 00:00:00.000
  khan      1953-07-24 00:00:00.000

Which dbms are you using? Too many products are far from ANSI SQL compliant when it comes to date/time. Data type for DOB column? — jarlh, Dec 16 '15 at 12:20
@Matt Gibson: You are right. And dotjoe's answer there looks better than any answer given here. — Thorsten Kettner, Dec 16 '15 at 13:18

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

1

for example, between 20 to 30

Use DATEDIFF:

SELECT Age = DATEDIFF(year, DOB, GetDate()),
       Between20_30 = CASE WHEN DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30
                      Then 'yes' ELSE 'no' END
FROM dbo.Table1

Demo

If you want to filter by persons who are between 20 and 30 use a WHERE clause:

WHERE DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30

Since DATEDIFF ist not precise(treats the month difference between 12/31 and 01/01 as one month), if that's an issue you could use this approach.

edited May 23 '17 at 12:32

Community

1
1

answered Dec 16 '15 at 12:23

Tim Schmelter

450,073
74
686
939

Watch out with using DATEDIFF. The age calculation will not be very precise this way. The function rounds off. – Tom Dec 16 '15 at 12:28
I need those record, who have age 20 to 30 years. – Mr doubt Dec 16 '15 at 12:30
@Tom: god point, if that's an issue he could use [this](http://stackoverflow.com/a/57720/284240) approach. – Tim Schmelter Dec 16 '15 at 12:31
@zahed: then use `WHERE DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30`. I've edited my answer to include it. – Tim Schmelter Dec 16 '15 at 12:32
SELECT Age = DATEDIFF(year, DOB, GetDate()), Between20_30 = where DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30 FROM Table_RegistrationInfo.. Incorrect syntax near the keyword 'where'. Getting error.. – Mr doubt Dec 16 '15 at 12:41
@zahed: `WHERE` comes always after the `FROM` clause. So: `SELECT ... FROM tbl WHERE DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30` – Tim Schmelter Dec 16 '15 at 12:49
SELECT Age = DATEDIFF(year, DOB, GetDate()), Between20_30 = CASE WHEN DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30 Then 'yes' ELSE 'no' END FROM Table_RegistrationInfo WHERE DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30. I got it. Check once my query. – Mr doubt Dec 16 '15 at 13:00
Please can you give vote for my question. @TimSchmelter – Mr doubt Dec 16 '15 at 13:15

score 0 · Answer 2 · answered Dec 16 '15 at 12:46

0

select GivenName,Age = DATEDIFF(year, DOB, GetDate())
FROM [dbo].[AgeCalc] where DATEDIFF(year, DOB, GetDate()) BETWEEN 20 AND 30

answered Dec 16 '15 at 12:46

Sudhir Panda

774
1
7
27

score 0 · Answer 3 · answered Dec 16 '15 at 12:59

0

Don't use DATEDIFF. When using DATEDIFF(year, ...) it is only the year part of the dates that is being considered. So it wouldn't matter which birthday exactly and you'd consider people 20 years old who are not yet full twenty years old.

Today 2015-12-26 you would want persons born between 1984-12-17 till 1995-12-16.

1984-12-17 (today minus 31 years plus one day) means the person gets 31 tomorrow
1995-12-16 (today minus 20 years) means the person just got 20 today.

So:

select *
from persons
where convert(varchar, dob, 102) 
        between convert(varchar, dateadd(day, 1, dateadd(year, -31, getdate())), 102)
            and convert(varchar, dateadd(year, -20, getdate()), 102);

(I must admit though that I am too lazy to think the February 29 thing through now.)

answered Dec 16 '15 at 12:59

Thorsten Kettner

89,309
7
49
73

Can you update answer according to above my table details. – Mr doubt Dec 16 '15 at 13:02
All I see in your table details is that the date of birth column is called DOB. I used that. So what else should I consider? – Thorsten Kettner Dec 16 '15 at 13:05
And here is a SQL fiddle illustrating that `DATEDIFF(year, ...)` is not appropriate for calculating the age: http://www.sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1/3740. – Thorsten Kettner Dec 16 '15 at 13:23
Please take above my question example. – Mr doubt Dec 16 '15 at 14:44
Sorry, I have no idea what you mean. You gave an example with a table containing GivenName and DOB and four records; I have given you a query to select persons between 20 and 30 years old from it. What else do you want? Have you tried my query and it doesn't work for you? If so, in what sense doesn't it work for you? – Thorsten Kettner Dec 17 '15 at 06:07

SQL Select query using current date time to get the users ages?

3 Answers3