Hmmm.... no matter what I did, I could not get the WHERE clause to use the DATEDIFF function without getting a conversion error message.
I re-did the process... here it is:
DECLARE @Statement NVARCHAR(4000),
@MyTable NVARCHAR(128),
@TableDate smalldatetime,
@DeleteDate smalldatetime,
@DataCntr INT,
@LoopCntr INT
SET @DeleteDate = '2007/11/01'
IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL
DROP TABLE #temptable;
CREATE TABLE #temptable ( primary_key INT IDENTITY(1,1) NOT NULL,
[MyTable] nvarchar(128) null)
INSERT into #temptable
SELECT name
FROM dbo.SysObjects
WHERE LEFT(Name,5) = 'CustA'
AND SUBSTRING(NAME,LEN(Name)-4,3) IN
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
-- Sent Counters for looping purposes
SET @DataCntr = ISNULL((Select count(*) from #temptable),0)
SET @LoopCntr = 1
WHILE @DataCntr > 0 AND @LoopCntr <= @DataCntr
BEGIN -- WHILE LOOP
---- CODE GOES HERE, this is an example
SELECT @MyTable = MyTable
FROM #temptable
WHERE primary_key = @LoopCntr
---- CODE GOES HERE
SET @TableDate = CONVERT(SmallDateTime,SUBSTRING(@MyTable,LEN(@MyTable)-4,3)+ ' 01, '+SUBSTRING(@MyTable,LEN(@MyTable)-1,2),101)
IF (DATEDIFF(MM,@DeleteDate,@TableDate) < 0)
BEGIN
PRINT 'Table '+@MyTable+' is older';
SET @Statement = 'DROP TABLE '+@MyTable;
PRINT @Statement;
--EXEC (@Statement);
END
ELSE PRINT 'Do not touch '+@MyTable
SET @LoopCntr = @LoopCntr + 1;
END -- WHILE LOOP
DROP TABLE #temptable;
Setting the CONVERT to a variable, then using it in the DATEDIFF function works fine!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)