0

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
('07/01/2016','07/05/2016')
,('07/01/2016','07/04/2016')
,('07/04/2016','07/08/2016')

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

Select * from #Moofasa

--Drop Table #Moofasa
4

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

CREATE TABLE #Moofasa
(
  gotitdate DATE,
  dunitdate DATE,
  delayed AS COALESCE( DATEDIFF(day, gotitdate, dunitdate), 0)
) ;

Tested at: rextester.com:

INSERT INTO #Moofasa 
  (gotitdate, dunitdate) 
VALUES
  ('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)  ;

SELECT * 
FROM #Moofasa ;

gives:

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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