Solved

Using IsEmpty

Posted on 2008-10-24
5
945 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:wyogirl77
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22800629
Well a datetime field cannot be empty.  It can either be NULL or a valid date.  If you are storing it in a string field (varchar) then you can test it with the len function.

But since you have time and date columns, and SQL doesn't support this, I guess that is what you are doing.  What format do you want your date in?


This will insert a string of each the current date and time into your table.
CREATE TRIGGER trg_Asgnmnt_OutOfOffice ON dbo.Asgnmnt

FOR UPDATE

AS

      IF UPDATE(OutOfOffice) BEGIN

            UPDATE      dbo.Assignee

               SET      OutOfOffice = I.OutOfOffice,

                  OutOfOfficeUntilTime = case when len(I.OutOfOfficeUntilTime)=0 then convert(char(8),getdate(),108) else I.OutOfOfficeUntilTime end,

                  OutOfOfficeUntilDate = case when len(I.OutOfOfficeUntilDate)=0 then convert(char(10),getdate(),101) else I.OutOfOfficeUntilDate end

            FROM      dbo.Assignee A,

                  INSERTED I

            WHERE      A.LoginID = I.LoginID

              AND      I.CallID = '00010000'

      END

GO

Open in new window

0
 

Author Comment

by:wyogirl77
ID: 22800713
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800770
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

0
 

Author Comment

by:wyogirl77
ID: 22813456
The date would need to be in mm/dd/yyyy
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22813504
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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now