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

LVL 1
GolfgentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

0
HainKurtSr. System AnalystCommented:
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

0
HainKurtSr. System AnalystCommented:
fix to above
DECLARE @CRITERIA DATETIME
DECLARE	@ROWCOUNT INT = 1000
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GolfgentAuthor Commented:
Thanks, it worked perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.