3

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

11

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:

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;
  • Thank you; I tend to forget how useful PRINT is. – Question3CPO Jan 24 '14 at 15:49
  • 2
    Anytime, any language, when there is generated execution code it's wise to always see what the final "code" is. +1 great approach, Aaron. – Thomas Stringer Jan 24 '14 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.