September 29, 2008 at 8:52 am
I have some tables in a database which contain the month and year, i.e., CustAddresses_Dec07, CustAddresses_Nov07, etc. as part of the name.
In trimming the database, I wish to delete some of the older table(s) using a drop. However, the problem is trying to identify the tables to drop.
Here is the code I was trying to use to select the tables. Assume that I wish to drop all tables prior to 11/1/07:
SELECT name
,SUBSTRING(NAME,LEN(Name)-4,3)
,CONVERT(SmallDateTime,SUBSTRING(NAME,LEN(Name)-4,3)+ ' 01, '+SUBSTRING(NAME,LEN(Name)-1,2),101) as CastName2Date
,CONVERT(SmallDateTime,'2007-11-01',101) as CastTimeEntered
,DATEDIFF(mm,CONVERT(SmallDateTime,'2007-11-01',101),CONVERT(SmallDateTime,SUBSTRING(NAME,LEN(Name)-4,3)+ ' 01, '+SUBSTRING(NAME,LEN(Name)-1,2),101))
,DATEDIFF(mm,'2007-11-01',SUBSTRING(NAME,LEN(Name)-4,3)+ ' 01, '+SUBSTRING(NAME,LEN(Name)-1,2))
--,DATENAME(MM,(SUBSTRING(NAME,LEN(Name)-4,3))+ '01 20'+SUBSTRING(NAME,LEN(Name)-1,2))
from dbo.SysObjects
WHERE LEFT(Name,5) = 'CustA'
AND SUBSTRING(NAME,LEN(Name)-4,3) IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
AND DATEDIFF(mm,'2007-11-01',SUBSTRING(NAME,LEN(Name)-4,3)+ ' 01, '+SUBSTRING(NAME,LEN(Name)-1,2)) < 0
The problem is with the DateDiff in the WHERE clause. The SELECT works fine in concatenating the name to get a DateDiff value, but when I try to use the DateDiff in the WHERE clause I receive a Conversion failed when converting datetime from character string. error message.
Any ideas? Comments?
Thanks!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
September 29, 2008 at 11:21 pm
Seems this script is working fine.Can you just send across your drop table script.
select ' drop table ' + name
from dbo.SysObjects
WHERE LEFT(Name,5) = 'CustA'
AND SUBSTRING(NAME,LEN(Name)-4,3) IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
AND DATEDIFF(mm,'2007-11-01',SUBSTRING(NAME,LEN(Name)-4,3)+ ' 01, '+SUBSTRING(NAME,LEN(Name)-1,2)) < 0
September 30, 2008 at 9:33 am
Hmmm.... no matter what I did, I could not get the WHERE clause to use the DATEDIFF function without getting a conversion error message.
I re-did the process... here it is:
DECLARE @Statement NVARCHAR(4000),
@MyTable NVARCHAR(128),
@TableDate smalldatetime,
@DeleteDate smalldatetime,
@DataCntr INT,
@LoopCntr INT
SET @DeleteDate = '2007/11/01'
IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL
DROP TABLE #temptable;
CREATE TABLE #temptable ( primary_key INT IDENTITY(1,1) NOT NULL,
[MyTable] nvarchar(128) null)
INSERT into #temptable
SELECT name
FROM dbo.SysObjects
WHERE LEFT(Name,5) = 'CustA'
AND SUBSTRING(NAME,LEN(Name)-4,3) IN
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
-- Sent Counters for looping purposes
SET @DataCntr = ISNULL((Select count(*) from #temptable),0)
SET @LoopCntr = 1
WHILE @DataCntr > 0 AND @LoopCntr <= @DataCntr
BEGIN -- WHILE LOOP
---- CODE GOES HERE, this is an example
SELECT @MyTable = MyTable
FROM #temptable
WHERE primary_key = @LoopCntr
---- CODE GOES HERE
SET @TableDate = CONVERT(SmallDateTime,SUBSTRING(@MyTable,LEN(@MyTable)-4,3)+ ' 01, '+SUBSTRING(@MyTable,LEN(@MyTable)-1,2),101)
IF (DATEDIFF(MM,@DeleteDate,@TableDate) < 0)
BEGIN
PRINT 'Table '+@MyTable+' is older';
SET @Statement = 'DROP TABLE '+@MyTable;
PRINT @Statement;
--EXEC (@Statement);
END
ELSE PRINT 'Do not touch '+@MyTable
SET @LoopCntr = @LoopCntr + 1;
END -- WHILE LOOP
DROP TABLE #temptable;
Setting the CONVERT to a variable, then using it in the DATEDIFF function works fine!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
September 30, 2008 at 9:35 am
Please ignore the CODE GOES HERE comments... I use templates when I create some of these scripts. 😉
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply