asked on

I'm having problems deleting rows from a MS SQL table based on smalldatetime older than X days

I have a temporary table that merges data into a permanent table using MS SQL Server 2008 Express. I have a column in the temporary table which is named Timestamp and the data type is smalldatetime. I want to have a procedure run that will query against the Timestamp column for dates older than 21 days and then delete those rows. This procedure will run on a daily basis. When I run the current code it returns a message stating command(s) completed successfully, however no data has changed or been deleted from the database table (Employees.dbo.TaskPLus_SurveyFeed).

DECLARE @CRITERIA DATETIME
DECLARE	@ROWCOUNT INT
SET @CRITERIA = CONVERT(VARCHAR(19),GETDATE()-22)
WHILE @ROWCOUNT = 1000
BEGIN
	DELETE TOP(1000)
	FROM Employees.dbo.IT_TaskPlus_SurveyFeed
	WHERE TIMESTAMP <@CRITERIA
	
	SELECT @RowCount = @@ROWCOUNT
END

Open in new window

Raja Jegan R

Try this

DECLARE @CRITERIA DATETIME
DECLARE @ROWCOUNT INT
SET @CRITERIA = CONVERT(VARCHAR(19),GETDATE()-22)

DELETE FROM Employees.dbo.IT_TaskPlus_SurveyFeed
WHERE TIMESTAMP <@CRITERIA

SELECT @RowCount = @@ROWCOUNT

Open in new window

HainKurt

try this

DECLARE @CRITERIA DATETIME
DECLARE	@ROWCOUNT INT
SET @CRITERIA = GETDATE()-22
WHILE @ROWCOUNT = 1000
BEGIN
	DELETE TOP(1000)
	FROM Employees.dbo.IT_TaskPlus_SurveyFeed
	WHERE TIMESTAMP < @CRITERIA
	
	SELECT @RowCount = @@ROWCOUNT
END

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Golfgent

ASKER

Thanks, it worked perfectly.