• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

SQL Server2005

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
onebite2
Asked:
onebite2
  • 2
1 Solution
 
WizillingCommented:
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
 
WizillingCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now