Solved

SQL Server2005

Posted on 2008-06-09
2
191 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
  • 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Finding chars in a column 2 67
Impove long SQL Stored Procedure Performance 14 78
create insert script based on records in a table 4 24
Query to return total 6 21
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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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