In my example syntax below I want the field delayed to be the difference in days between gotitdate and dunitdate I know that this syntax will get me the result, and I was going to set this up as an OnInsert() and a AfterUpdate() trigger however my two fields are not required! I can not alter table structure to make them be required.

Question at hand, how can I use this update syntax to account for a null date, to update to 0 if either field is null or update to the DateDiff() if both fields are not null? And as always, if there is a better approach for this methodology I am open to suggestions:

Create Table #Moofasa
  gotitdate date
  ,dunitdate date
  ,delayed int

Insert Into #Moofasa (gotitdate, dunitdate) Values

Update #Moofasa 
Set delayed = DateDiff(d, gotitdate, dunitdate)
WHERE delayed is null

Select * from #Moofasa

--Drop Table #Moofasa

1 Answer 1


A computed column would solve the issue and probably be better than having triggers:

  gotitdate DATE,
  dunitdate DATE,
  delayed AS COALESCE( DATEDIFF(day, gotitdate, dunitdate), 0)
) ;

Tested at: rextester.com:

  (gotitdate, dunitdate) 
  ('2016-07-01', '2016-07-05'),
  ('2016-07-01', '2016-07-04'),
  ('2016-07-04', '2016-07-08'),
  (NULL,         '2016-07-09'),
  ('2016-07-09', NULL),
  (NULL,         NULL)  ;

FROM #Moofasa ;


gotitdate   dunitdate   delayed
01.07.2016  05.07.2016  4 
01.07.2016  04.07.2016  3 
04.07.2016  08.07.2016  4 
NULL        09.07.2016  0
01.07.2016  NULL        0
NULL        NULL        0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.