Calculate Age From Birthdate Field

Hi all,

I€™m trying to get a users age from their date of birth which is stored in my table, and although I can get the age I can€™t actually use it in a where clause as it keeps stating €˜invalid column name age€™. Below is a much reduced version of my code that shows my problem in a clearer way (the where clause is for example purposes as I don€™t intend to use it like this but it does show my problem):

SELECT DATEDIFF(YY, '8/5/1971', GETDATE()) - CASE WHEN (MONTH('8/5/1971') = MONTH(GETDATE()) AND DAY('8/5/1971') > DAY(GETDATE()) OR MONTH(GETDATE()) > MONTH('8/5/1971')) THEN 1 ELSE 0 END AS Age
WHERE Age = 35

Now this does get the persons correct age for the time of year and works fine until I include the WHERE clause, that is when I get the error. You can see that I have alias the returned column with the name Age but I keep getting the €˜invalid column name Age€™ error. What is wrong and how can I sort it out?

Now I did manage to resolve this problem using a temporary table (full actual code below) but I€™m certain my resolution can€™t be the best or easiest way to do this. Please help! Thanks

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROC [dbo].[sc_NewlyJoinedPeopleSearch]
@UserID uniqueidentifier,
@Days tinyint
AS
BEGIN
DECLARE @AgeFrom tinyint
DECLARE @AgeTo tinyint
DECLARE @DateToday DateTime
SET @AgeFrom = (SELECT AgeFrom FROM Users WHERE UserID = @UserID)
SET @AgeTo = (SELECT AgeTo FROM Users WHERE UserID = @UserID)
SET @DateToday = GETDATE()

SELECT UserID, DATEDIFF (YY, DateOfBirth, @DateToday) - CASE WHEN (MONTH(DateOfBirth)=MONTH(@DateToday) AND DAY(DateOfBirth) > DAY(@DateToday) OR MONTH (DateOfBirth) > MONTH (@DateToday)) THEN 1 ELSE 0 END AS UsersAge
INTO #Age
FROM Users

SELECT Users.UserID, UserName, AgeFrom, AgeTo
FROM Users JOIN #Age ON #Age.UserID = Users.UserID JOIN aspnet_Membership ON Users.UserID = aspnet_Membership.UserID
WHERE UsersAge > 30
ORDER BY CreateDate desc, Users.TimeStampUsers desc
DROP TABLE #Age
END