Select users between age range.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select users between age range.

Author

Topic

rloveless
Starting Member

2 Posts

Posted - 2010-11-24 : 16:58:01

Hi I have a Table "Users" which has a column BirthDate as a dateTime. How might I write a select statement that gets all users between the age of 20 - 25. Is should be correct to the day. This might be a little tricky because of leap years.
Thanks you much for help :)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-24 : 17:41:29

http://stackoverflow.com/questions/57599/how-to-calculate-age-in-tsql-with-years-months-and-days

That might be useful.

Poor planning on your part does not constitute an emergency on my part.

rloveless
Starting Member

2 Posts

Posted - 2010-11-24 : 17:58:58

Hmm, that looks pretty complex. Hopefully there's a cleaner solution out there. (fingers crossed) :)

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-24 : 18:16:09

Well, it would have been if you posted ddl and sample data

But something like

WHERE Birth_Dt BETWEEN DATEDIFF(yyyy,-20,GetDate()) AND DATEDIFF(yyyy,-25,GetDate())

NOT Tested

If I got it wrong look it up in BOL...I'm not logged in

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-30 : 10:44:07

quote:
Originally posted by rloveless

Hi I have a Table "Users" which has a column BirthDate as a dateTime. How might I write a select statement that gets all users between the age of 20 - 25. Is should be correct to the day. This might be a little tricky because of leap years.
Thanks you much for help :)

First thing to do is change the column to DATE now that we have ANSI/ISO temporal data types. This makes the math much cleaner.

Second thing is to define "age"; Westerners usually count whole years and Orientals count the year you are in. For example, someone who has lived 59.5 years is 59 in the West and in his 60-th year in the orient.

Here is one way:

birth_date BETWEEN DATEADD(YEAR, 20, CAST (CURRENT_TIMESTAMP AS DATE))
AND DATEADD(YEAR, 25, CAST(CURRENT_TIMESTAMP AS DATE))

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources