Calculating Age based on DOB

[Elroacho]

Hi,

I have a table that contains a date of birth. I would like to add another column for age and if possible automatically, maybe using a column formula, calculate the age based on the DOB column value.

Can this be done this way?

Cheers,
Kevin.

 

[Elroacho]

Sorry, Just to add that I am using SQL Server.

Cheers,
K

 

[JarlH]

You don't have to add another column to your table. Create a view instead, which includes this age column.

Something like:

CREATE VIEW view-name AS
SELECT col1, col2, DOB, some-calculation-to-get-age AS age
FROM table-name

 

[Elroacho]

Hi,

Not a bad idea but seems overkill as there are a large number if fields in the table. I have read that I can put a formula in the table definition which should do pretty much what I want.

The only thing I need now is to figure out a way of calculating the age. Any ideas or know of any functions for this?

Cheers,
Kevin.

 

[JarlH]

Overkill? Views are excellent, especially in cases like this.

If you get tired of specifying all column names in the create view statement you can even use the table-name.* shortcut.

CREATE VIEW view-name AS
SELECT t.*, expression-to-calculate-age AS age
FROM table-name AS t

 

[Elroacho]

OK here is the solution I've applied and it woks well

I have created a user function as follows

CREATE 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 created a new column called AGE.
I am using Toad so I have the option in the alter table dialog to set the column function to the following

([dbo].[fn_GetAge]([DOB], getdate()))

this works a treat.

Thanks for your help. I would like credit a user on another forum for the function code but it's getting late (12.20am) and I acidentally closed the web page down before I took down the name and link. So I'm off to bed Zzzzzzz

YNWA,
Kevin.