Re: => Trigger to calculate datediff

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 20:04:53 +0530

hi rhonda,

modify your trigger body as follows.

CREATE TRIGGER trgCalculateLostDays ON incident
FOR INSERT, UPDATE
AS
UPDATE a
SET a.DaysLost = case when a.DateTo Is Null
                               then datediff("d", a.DateFrom, {fn
current_date()})
                               else (datediff("d", a.DateFrom, a.DateTo) +1)
end
from Incident a join inserted b
on a.pk_id= b.pk_id --THIS WILL BE PRIMARY KEY OF THE TABLE.

go

The same can be accomplished using computed column
ex:

create table incident(pk_id int identity primary key,
datefrom datetime,
dateto datetime,
dayslost as case when DateTo Is Null then datediff("d", DateFrom,
getdate())
                               else (datediff("d", DateFrom, DateTo) +1)
end ) 

-- 
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com