|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, August 27, 2008 11:47 AM
Points: 2,
Visits: 10
|
|
Hi
I have 2 columns 'Date of Birth' & 'Age' on a table.
I want to calculate age as current date- 'date of birth'.
I was hoping to set a formula for the 'Age' column so that this value is computed/updated automatically.
can anyone tell me how to do this using the 'computed column specification' in sql server 2005 ?
Pardon me if my questions sounds very basic (& stupid:) )but I am a total newbie to SQLserver2005 & am having problem with the syntaxes
Thanks,
Prasad
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, February 18, 2010 4:19 AM
Points: 375,
Visits: 1,249
|
|
Hi Pujari,
use this sample:
create table abc
(
id int,
dob datetime,
age as datepart(yy,getdate()) - datepart(yy, dob)
)
--
insert into abc
values (1, '01/01/1990')
--
select * from abc
--
drop table abc
Please let me know if you have any concern..
Cheers!
Sandy.
--
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Tuesday, February 23, 2010 10:36 AM
Points: 462,
Visits: 778
|
|
The only problem with using DateDiff like that is that if the date being examined is 2007-12-31 and "today" is 2008-01-01, the age will be one. That may not be what you want. A good article can be found here. IIRC, it will round off the age, so that after something is N years and 6 months old, the age computed will be N+1. That also may not be what you want, as we tend to think of our own age as being incremented on our birthday and not a day sooner -- much less 6 months sooner. :P
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, February 18, 2010 4:19 AM
Points: 375,
Visits: 1,249
|
|
Yes, you can use this also.
declare @dob datetime,
@age int,
@day datetime
set @day = '2008-02-28'
set @dob = '2007-03-01'
set @age = datediff(yy,@dob,@day) -
case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end
select @age
Cheers!
Sandy.
--
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, August 27, 2008 11:47 AM
Points: 2,
Visits: 10
|
|
Thanks Sandy & Tomm
That was really helpful. I am going ahead with the 2nd option.
It is really encouraging to see veteran users on this forum replying to newbies' questions!:)
Thanks again!
Prasad
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, November 10, 2009 11:16 PM
Points: 1,
Visits: 0
|
|
| Hi all... i would like to know abt the performance cost for computed column.. is there any possibility of Increase or decrease in performance b,coz of computed column???
|
|
|
|
