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).
Best Answer
You should try printing your commands when they yield errors. If you issued this instead of executing it:
You would see that this:
Yields this:
Which, since those look to SQL Server like three integers with a couple of subtraction operators, becomes:
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.
If any of these columns are
DATETIME
, notDATE
, you really should not be usingBETWEEN
at all. Instead: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):