BEGIN TRAN
CREATE SCHEMA template
GO
CREATE TABLE template.Test (id int)

CREATE TABLE dbo.Test_20090101 (id int) 
CREATE TABLE dbo.Test_20090102 (id int) 
CREATE TABLE dbo.Test_20090103 (id int) 
CREATE TABLE dbo.Test_20090104 (id int) 
CREATE TABLE dbo.Test_20090105 (id int) 

DECLARE	  @OldestPartitionDate			DATETIME

SET @OldestPartitionDate = CURRENT_TIMESTAMP-2

SELECT		tbls.object_id, tbls.[name]
,CONVERT(VARCHAR,RIGHT(tbls.[name],8),112)
FROM		sys.tables sysTbls 
INNER JOIN	sys.schemas schm ON schm.schema_id = sysTbls.schema_id
INNER JOIN	sys.tables	tbls ON REPLACE(tbls.[name],RIGHT(tbls.[name],9),'') = sysTbls.[name]
WHERE		schm.[name] = 'template'
AND			CAST(RIGHT(tbls.[name],8)AS DATETIME) < @OldestPartitionDate

ROLLBACK TRAN

quote:

---

Originally posted by Bex

Hi Madhivanan

That works!!!! Thank you!

Could you explain to me WHY that works? I Understand that you're checking that the value is in fact a date, then checking the length, and then casting the value.

However, why would we need to check the length if we have already confirmed that the value is in fact a date? What is going on within SQL Server????



Hearty head pats

---