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