Solved

SQL Server2005

Posted on 2008-06-09
2
197 Views
Last Modified: 2010-03-19
I need to implement a  delete proc to delete all records from a table which are older than 60days.
I just wrote a proc and compared with a field (dtInsert) from the table.....I feel I went wrong somewhere....I need help to correct this proc.....
ALTER PROCEDURE [dbo].[pr_Expire]
 
AS
BEGIN
    SET NOCOUNT ON;
    
    --Calculate expiration date   
    DECLARE @expire DATETIME
    SEt @expire = GETDATE()
    SET @expire = DATEADD(day,-60,@expire)
 
 
    --Expire all records older than @expire
    DELETE FROM dbo.CP_EquiP
     WHERE dtInsert < @expire
END

Open in new window

0
Comment
Question by:onebite2
[X]
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
  • 2
2 Comments
 
LVL 13

Expert Comment

by:Wizilling
ID: 21748051
The procedure seems correct. why do u think u went wrong somewhere..
or is it that u had to include the -60th day also .. in that case u need to change your where clause to
WHERE dtInsert <= @expire
0
 
LVL 13

Accepted Solution

by:
Wizilling earned 500 total points
ID: 21748118
In fact to include the 60th day as well you will need to calculate the 59th day and convert it to 000 hours ie mid night and query everythnig less then it.
ALTER PROCEDURE [dbo].[pr_Expire]
 
AS
BEGIN
    SET NOCOUNT ON;
    
    --Calculate expiration date   
    DECLARE @expire DATETIME
    SEt @expire = CONVERT(varchar(25), GETDATE(),112)
    SET @expire = DATEADD(day,-59,@expire)
  
    --Expire all records older than @expire
    DELETE FROM dbo.CP_EquiP
     WHERE dtInsert < @expire
END

Open in new window

0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 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