Col2 = Col1+3Years... how?

Hello

I have two columns of type (DATE).

I want to have a trigger that updates [Column2] with [Column1] value + 3 years.

What is the SQL syntax for that?
alfardanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
try this

Update Tablename
Column2 = dateadd(year,3,Column1)

0
BrandonGalderisiCommented:
You can either:

A> Make "Column2" a computed column of dateadd(yy,3,[column1])

Use a trigger
create trigger SomeTrigger on SomeTable
for insert,update
as
if @@rowcount=0
  return
set nocount on 
update ST
set column2 = dateadd(yy,3,column1)
from SomeTable ST
join inserted I
on st.PrimaryKeyField = I.PrimaryKeyField

Open in new window

0
rob_farleyCommented:
I wouldn't worry about the @@rowcount bit...

create trigger SomeTrigger on SomeTable
for insert,update
as
update ST
set col2 = dateadd(year,3,I.col1)
from SomeTable ST
join inserted I
on st.PrimaryKeyField = I.PrimaryKeyField
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

rob_farleyCommented:
Oh, and a bit of background info...

the 'inserted' table is one that is used internally to perform changes. It's accessible in the OUTPUT clause and in triggers.

So by joining to it, you're saying "Just update the records that have been affected by this insert/update"

Rob
0
BrandonGalderisiCommented:
Why not worry about @@rowcount.  While in this case there is very little logic being done, checking @@rowcount is a quick exit strategy to short-circuit a trigger when a statement like this is run.


Update Sometable
set somefield = 'SomeValue'
where 1=0
0
BrandonGalderisiCommented:
Obviously, bad example.  But if an insert or update statement effects no records, my trigger will exit immediately.
0
rob_farleyCommented:
Sure, but if inserted is empty, the update statement within is basically no work either, and you haven't had to check the @@rowcount variable.

I don't see a need to check for a condition which won't change behaviour, when that condition will occur almost never anyway.

It's valid to check it - I just don't see it as important, especially when the key part of the trigger is the use of the 'inserted' table.

I'm not saying you're wrong at all, and would recommend the author give you the points for your trigger.

Rob
0
alfardanAuthor Commented:
BrandonGalderisi:
I believe your solution worked just fine for me, but I noticed that if I inserted (for example) '12/31/2009' in Col1, then Col2 becomes '12/31/2012'
How can I let this Col2 be 3 years minus 1 day?
0
rob_farleyCommented:
Just subtract a day from it.

set col2 = dateadd(day,-1,dateadd(year,3,I.col1))
0
BrandonGalderisiCommented:
You can simply do a -1 on Column1 prior to the dateadd(yy.....)

It's practically the same thing as doing two dateadd function calls.


I'm guessing based upon your needs that you will be using some sort of...

VALUE between column1 and column2

... comparison.  If that's the case, I'd like you to consider using

value >= column1 and value < column2

In order to have non overlapping values.
create trigger SomeTrigger on SomeTable
for insert,update
as
if @@rowcount=0
  return
set nocount on 
update ST
set column2 = dateadd(yy,3,column1-1)
from SomeTable ST
join inserted I
on st.PrimaryKeyField = I.PrimaryKeyField

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.