asked on

Using IsEmpty

I have the following trigger, CREATE TRIGGER trg_Asgnmnt_OutOfOffice ON dbo.Asgnmnt
FOR UPDATE
AS
      IF UPDATE(OutOfOffice) BEGIN
            UPDATE      dbo.Assignee
              SET      OutOfOffice = I.OutOfOffice,
                  OutOfOfficeUntilTime = I.OutOfOfficeUntilTime,
                  OutOfOfficeUntilDate = I.OutOfOfficeUntilDate
            FROM      dbo.Assignee A,
                  INSERTED I
            WHERE      A.LoginID = I.LoginID
              AND      I.CallID = '00010000'
      END
GO

If OutOfOfficeUntilDate is empty (not NULL I just found it won't be NULL) I need to set it to today's date, what would be the proper syntax i was thinking an if statement after, OutOfOfficeUntilDate = I.OutOfOfficeUntilDate but wasn't sure what the else part of the if statement should be? Also i only want to change the value of OutOfOfficeUntilDate if that field is empty otherwise I want to keep what is in there.

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wyogirl77

ASKER

The time I don't really care about so much, it is the date, which you are correct I have it in a Varchar(10) field because that is what the program I am working with stores it in, does that change your suggestion at all?

BrandonGalderisi

Only to know what format you want your date in.

There's lots of options and it has to be consistent with what you have in order to be converted back into a date later down the line.

ex.
yyyy-mm-dd
mm-dd-yyyy
mm/dd/yyyy
dd-mm-yyyy
dd/mm/yyyy

wyogirl77

ASKER

The date would need to be in mm/dd/yyyy

BrandonGalderisi

That is format option 101 which is what I have above. I still think it would be a good idea to store it as a single datetime field.