calculating age using sql server function

Hi ya,

I have a table which will be having DOB and I want to calculate the current age of persons, i want to save it into currentage as default.

I have searched over the interent and find some solutions but in my database the DOB is stored as = 'dd/mm/yyyy' and none of the solutions work on this.

Any ideas?

Prince:

I assume by your post that your DOB is stored as a string. Try:

select datediff (year, convert (datetime, '07/15/1908'), getdate())

Dave

|||

Mugambo wrote:

Prince:

I assume by your post that your DOB is stored as a string. Try:

select datediff (year, convert (datetime, '07/15/1908'), getdate())

Dave

Hello Dave,

No you got me wrong, the DOB is stored as Datetime in Sql 2005, the other thing is that datediff is not going to calculate right and thirdly my dates are stored as datetime but in dd/mm/yyyy format.

You can try it by yourself.

|||

First, create this user defined function.

ALTER function [dbo].[fn_GetAge]

(@.in_DOB AS datetime,@.now as datetime)

returns int

as

begin

DECLARE @.age int

IF cast(datepart(m,@.now) as int) > cast(datepart(m,@.in_DOB) as int)

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)

else

IF cast(datepart(m,@.now) as int) = cast(datepart(m,@.in_DOB) as int)

IF datepart(d,@.now) >= datepart(d,@.in_DOB)

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)

ELSE

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) -1

ELSE

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) - 1

RETURN @.age

end

-

Then, to use it...

Select DOB, dbo.fn_GetAge(DOB, GetDate()) As Age From Table

Since all calculations are based on DateTime's, your formatting shouldn't matter

|||

thanks mastros,

it works really great.