Database

USE pubs<br />
IF EXISTS (SELECT name FROM sysobjects <br />
      WHERE name = 'au_info2' AND type = 'P')<br />
   DROP PROCEDURE au_info2<br />
GO<br />
USE pubs<br />
GO<br />
CREATE PROCEDURE au_info2<br />
   @lastname varchar(30) = 'D%',<br />
   @firstname varchar(18) = '%'<br />
AS <br />
SELECT au_lname, au_fname, title, pub_name<br />
FROM authors a INNER JOIN titleauthor ta<br />
   ON a.au_id = ta.au_id INNER JOIN titles t<br />
   ON t.title_id = ta.title_id INNER JOIN publishers p<br />
   ON t.pub_id = p.pub_id<br />
WHERE au_fname LIKE @firstname<br />
   AND au_lname LIKE @lastname

delete a, b, c
from Account as a inner join Campaign as b
on a.[id] = b.[id] inner join CampaignSpec as c
on b.[id] = c.campaignid

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

SELECT * FROM branch 
WHERE DATEDIFF('dd', GETDATE(), EndDate) = ContractReminder 
OR DATEDIFF ('dd', GETDATE(), EndDate) = ReminderType * 30

SELECT BranchName, ContractNo, Email, StartDate, EndDate, 
    CASE
-- notice that the 101 is my favorite see BOL Convert for list of others
        WHEN CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate 
                 THEN 'Less than ' + CAST(ContractReminder as VARCHAR) + ' days left'
        WHEN CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate 
                 THEN 'Less than ' + remindertype + ' months left'
    END AS MyFlag  
INTO #RList 
WHERE CONVERT(VARCHAR, DATEADD(day, ContractReminder, GETDATE()), 101) > EndDate 
    OR CONVERT(VARCHAR, DATEADD(month, remindertype, GETDATE()), 101) > EndDate 

select branchname, contractno, email, startdate, enddate, contractreminder, remindertype
from branch
where convert(varchar(20), enddate,112) = convert(varchar920), dateadd(dd, contractreminder, current_timestamp),112)  
      -- this will then removes the issues of the times in the datetime column