Delete old records in SQL database 3

[ironhide1975]

I'm trying to setup a way for SQL server to automatically delete records that are older than 30 days. The current working script I have is.

CREATE PROCEDURE [dbo].[deleteOldRecords] AS

@CutOffDate = DATEADD(dd, -30, CURRENT_TIMESTAMP)

DELETE FROM TrackUser WHERE DateTime < @CutOffDate

Can anyone help me write this? Much appreciated if so.

Therm-o-disc -

http://www.tod.com

 

[JayKusch]

CREATE PROCEDURE [dbo].[deleteOldRecords] AS

@CutOffDate = DATEADD(dd, -30, GetDate())

DELETE
FROM TrackUser
WHERE
CONVERT(Char,DateTime,110) < CONVERT(Char,@CutOffDate,110)


Thanks

J. Kusch

 

[SQLBill]

What's wrong with what you have?

Only question I have is 'what is CURRENT_TIMESTAMP'? I would use GETDATE() which is the system date/time.

-SQLBill

 

[ironhide1975]

I get a must declare CutOffDate error. Am I missing something?

Therm-o-disc -

http://www.tod.com

 

[ironhide1975]

Realize too DateTime is an actual table field name.

Therm-o-disc -

http://www.tod.com

 

[ecobb]

Just declare your variable before you try to set it

CREATE PROCEDURE [dbo].[deleteOldRecords] AS

DECLARE @CutOffDate smalldatetime
@CutOffDate = DATEADD(dd, -30, GetDate())

DELETE 
FROM TrackUser 
WHERE 
CONVERT(Char,DateTime,110) < CONVERT(Char,@CutOffDate,110)

Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[ironhide1975]

I figured out how to accomplish this using a SQL job. Thanks guys for your help.

Therm-o-disc -

http://www.tod.com