• 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)