SQL Trigger for DateDiff

I'm confused.  You talk about the difference in hours as a decimal, but you say that comm_allocated should be set to the datediff in minutes.  This sets it to minutes.

Just set your primary key field(s) in the ON statement of the join.

create trigger trg_insupd_communication on communication
for insert,update
as
if @@Rowcount=0
  return
set nocount on
if update(comm_datetime) or update(comm_todatetime)
update c
set comm_allocated = datediff(mi,comm_datetime,comm_todatetime)
from communication c
join inserted i
on c.PKField = i.PKField

Select allOpen in new window

I do apologise.
My example was wrong that would work out at 0.17

comm_datetime in the SQL database is '16/12/2008 12:00:12'
comm_todatetime in the SQL database is '16/12/2008 16:30:22'

Would be 4.5

The times are always in 30 minute slots

I have changed the trigger to

create trigger trg_insupd_communication on communication
for insert,update
as
if @@Rowcount=0
  return
set nocount on
if update(comm_datetime) or update(comm_todatetime)
update c
set comm_allocated = datediff(hh,c.comm_datetime,c.comm_todatetime)
from communication c
join inserted i
on c.comm_communicationid = i.comm_communicationid

But it seems to round down.

I've even tried
set comm_allocated = datediff(mi,c.comm_datetime,c.comm_todatetime) / 60

Regards,

Mim

Thats perfect! Thanks for your help and patience.

Mim