Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Capturing TableName MonYY Name Expand / Collapse
Author
Message
Posted Monday, September 29, 2008 8:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:59 AM
Points: 189, Visits: 1,207
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)
Post #577764
Posted Monday, September 29, 2008 11:21 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 5:16 AM
Points: 1,090, Visits: 388
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


Post #578076
Posted Tuesday, September 30, 2008 9:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:59 AM
Points: 189, Visits: 1,207
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)
Post #578402
Posted Tuesday, September 30, 2008 9:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:59 AM
Points: 189, Visits: 1,207
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)
Post #578407
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse