Post reply

enterprise manager column formula

  • May 19, 2008 at 5:04 pm

    #191106

    column1 data type datetime

    column2 data type datetime

    looking for formula for column2 to add + 25 years when user keys in to column1

    Column1

    dateadd(yyyy, 25, column2)

    Any help greatly appreciated.

  • May 19, 2008 at 5:18 pm

    #817523

    This way, users cannot enter anything directly into column2. Fair warning, there are times when you do NOT want computed columns.

    create table ComputedColEx

    (

    row int identity(1,1),

    co11 datetime,

    col2 as dateadd(yyyy, 25, co11)

    )

    insert into ComputedColEx

    select getdate()

    union select '2000-01-01'

    select * from ComputedColEx

  • May 20, 2008 at 1:36 am

    #817620

    or use a trigger.

    "Keep Trying"

  • May 21, 2008 at 10:39 am

    #818728

    Great start...but when I implement it with my software I get -271 error. Can't modify a computed column.

  • May 21, 2008 at 11:09 am

    #818747

    If the column1 + 25 years date just a starting point that can be changed by inserts and updates then it can't be a computed column. Use a trigger as Chirag suggested.

    If the column1 + 25 years date relationship is fixed then the computed column will work. However, you have to remove column2 from all of your inserts and updates (it can stay in the where and join parts, but not in the set column2 = anyval part).

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

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