Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using IsEmpty

Posted on 2008-10-24
5
947 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql, case when & top 1 14 26
SSMS Opening Mode 9 18
Query for timesheet application 3 17
In sql, how to roll up multiple rows to only one row. 4 35
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 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