Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server2005

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

604 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