Valleriani
asked on
MS-SQL: Query to find a age group?
MSSQL 2008.. I have a table with data which has name, category, and date of birth.
The date of birth is, for example:
12/24/1970
11/10/1965
What I'm trying to do is find dates between, lets say, age 30-45 ... I guess I would do something like search between 1965-1980 of the current date (June 05th). Is this possible, and if so , how would this be handled in a query?
The date of birth is, for example:
12/24/1970
11/10/1965
What I'm trying to do is find dates between, lets say, age 30-45 ... I guess I would do something like search between 1965-1980 of the current date (June 05th). Is this possible, and if so , how would this be handled in a query?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually its 5/14/2010 10:50 that is failing
ASKER
Nevermind, got it :)
>>When a DOB includes 'time' for some reason, is there a way to ignore time?<<
There are a couple of ways to handle that:
1. If you are using SQL Server 2008 than use DATE as in:
WHERE DOB BETWEEN DATEADD(year, -45, CAST(GETDATE() AS date)) AND DATEADD(year, -30, CAST(GETDATE() AS date))
2. If you are not using SQL Server 2008, use something like this:
WHERE DOB BETWEEN DATEADD(year, -45, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))) AND DATEADD(year, -30, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())))
There are a couple of ways to handle that:
1. If you are using SQL Server 2008 than use DATE as in:
WHERE DOB BETWEEN DATEADD(year, -45, CAST(GETDATE() AS date)) AND DATEADD(year, -30, CAST(GETDATE() AS date))
2. If you are not using SQL Server 2008, use something like this:
WHERE DOB BETWEEN DATEADD(year, -45, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))) AND DATEADD(year, -30, DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())))
ASKER
When a DOB includes 'time' for some reason, is there a way to ignore time? Like 12/12/1976 10:40 .. What is happening is IsDate() (To make sure theres a date there before hand) fails.