Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- -- Only the WHERE clause changed:
- WHERE ValueDate BETWEEN CAST(' + CONVERT(VARCHAR,@b,121) + ' AS DATE) AND CAST(' + CONVERT(VARCHAR,@E,121) + ' AS DATE)'
- PRINT @cmd;
- WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)
- WHERE ValueDate BETWEEN 2012-07-01 AND 2014-01-25
- WHERE ValueDate BETWEEN 2004 AND 1988
- 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;
- WHERE ValueDate >= @b AND ValueDate < DATEADD(DAY, 1, @e);
- 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;
Add Comment
Please, Sign In to add comment