Delete All Tables With Dates By Date

Refer to this question on SO because it's similar.

I'm working on a way to delete a range of dates from all tables with date values in a database (purpose for building mini-devs). I built a script that allows the user to pass in begin, end and database values, but am running into issues with dynamic sql. For instance, when removing records between certain dates, I get an operand clash. The below shows an over simplified example of this:

DECLARE @b DATE, @e DATE, @cmd NVARCHAR(MAX)
SET @b = '2012-07-01'
SET @e = '2014-01-25'

SET @cmd = 'DELETE FROM DateTable
WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

EXEC sp_executesql @cmd

The error is Operand type clash: date is incompatible with int. I tried doing an inner CAST within the dynamic sql since the error indicates that the ValueDate becomes and INT:

-- Only the WHERE clause changed:
WHERE ValueDate BETWEEN CAST(' + CONVERT(VARCHAR,@b,121) + ' AS DATE) AND CAST(' + CONVERT(VARCHAR,@E,121) + ' AS DATE)'

The error this time is Explicit conversion from data type int to date is not allowed. Finally I tried playing with some other formats (from MSDN). In a nutshell, I want to do exactly what the SO user was trying to accomplish, but be able to do it across multiple tables (I even searched for a script as I doubt I'm the first person to want this).

You should try printing your commands when they yield errors. If you issued this instead of executing it:

PRINT @cmd;

You would see that this:

WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

Yields this:

WHERE ValueDate BETWEEN 2012-07-01 AND 2014-01-25

Which, since those look to SQL Server like three integers with a couple of subtraction operators, becomes:

WHERE ValueDate BETWEEN 2004 AND 1988

Which isn't even a valid BETWEEN operation, even if it could translate those to dates.

What you should be doing is passing these as proper parameters, e.g.

SET @cmd = 'DELETE FROM dbo.DateTable -- always use SCHEMA prefix
 WHERE ValueDate BETWEEN @b AND @e;';

EXEC sp_executesql @cmd, N'@b DATE, @e DATE', @b, @e;

If any of these columns are DATETIME, not DATE, you really should not be using BETWEEN at all. Instead:

WHERE ValueDate >= @b AND ValueDate < DATEADD(DAY, 1, @e);

Please read:

• https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length

• https://sqlblog.org/2009/10/11/bad-habits-to-kick-avoiding-the-schema-prefix

• https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

• https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons

As for the overall requirement of repeating this query against all date columns in the database, here is how I would do it (I've looked at the SO question you reference, and it just shows how to do this for one table):

DECLARE @b DATE = '2012-07-01', @e DATE = '2014-01-25', 
        @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
DELETE ' + QUOTENAME(s.name) 
 + '.' + QUOTENAME(t.name) + '
  WHERE ' + QUOTENAME(c.name) + ' >= @b
  AND ' + QUOTENAME(c.name) + ' < DATEADD(DAY, 1, @e);'
 FROM sys.tables AS t
 INNER JOIN sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 WHERE c.system_type_id IN (40,42,43,58,61);
       -- or just = 40 if you're only interested in DATE columns

PRINT @sql;
-- EXEC sp_executesql @sql, N'@b DATE, @e DATE', @b, @e;

StackExchange Database Administrators Q#57488, answer score: 11