?
Solved

SQL Server2005

Posted on 2008-06-09
2
Medium Priority
?
200 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 1500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

743 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