Post reply

using 'Computed Column Specification '

  • July 14, 2008 at 10:58 am

    #195269

    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

  • July 15, 2008 at 1:42 am

    #842863

    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.

    --

  • July 16, 2008 at 2:31 am

    #843597

    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[/url]. 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. 😛

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • July 16, 2008 at 2:43 am

    #843602

    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.

    --

  • July 17, 2008 at 7:44 am

    #844389

    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

  • November 10, 2009 at 11:21 pm

    #1077648

    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???

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply