Discovering AGE based on date columns

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 2005 Forums

Transact-SQL (2005)

Discovering AGE based on date columns

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-05-28 : 21:50:58

Hello.

I have three INT columns in a table that record the users birth year, month, and day.

BDAY_DAY (INT)
BDAY_YEAR (INT)
BDAY_MONTH (INT)

I'd like to include a function in my query that will return their Age in years based on these three columns.

I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.

Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int
AS
Begin
Declare @Age As Int
Set @Age = Year(@Today) - Year(@DOB)
If Month(@Today) < Month(@DOB)
Set @Age = @Age -1
If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB)
Set @Age = @Age - 1
Return @AGE
End

Usage (how do i pass the three columns into this function??)


SELECT Last_Name, First_Name, ssn, dob
FROM Employee_Data e (nolock)
WHERE Cust_Id = 'Customer1'
and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 00:02:00

[code]Create FUNCTION dbo.GetAge (@Year int,@Month int,@Day int)
RETURNS Int
AS
Begin
Declare @Age As Int
Set @Age = Year(GETDATE()) - @Year
If Month(GETDATE()) < @Month
Set @Age = @Age -1
If Month(GETDATE()) = @Month and Day(GETDATE()) < @Day
Set @Age = @Age - 1
Return @AGE
End[/code]