CODEWITHSUNDEEP
sqlsql-servert-sql
Ali
Ali

Reputation: 139

TSQL List Result based on date in name field minus GETDATE() function

In our QA environment we restore databases for Developers and then drop them if the restore was done > 30 days. The naming convention for these restored databases are MyCompany-HA_DBName_20191114_FirstL.

I need to create a script to drop the database that are older than 30 days and run this script via SQL Agent job everyday to find and drop databases older than 30 days. However, my concern is that there are other databases with similar naming convention (does not include date in their name) that will get deleted. I have excluded all important databases in my query in where clause but I'm concern with new database that will be added to the server that should not be deleted.

I need help with returning only databases with date in their name which are older than 30 days. I have so far created the below query but it is not returning some databases. I used the like operator but something is not correct.

Any help is appreciated.

IF OBJECT_ID('tempdb..#ListofDbsToDrop') IS NOT NULL DROP Table #ListofDbsToDrop
SELECT * INTO #ListofDbsToDrop 
FROM ( 
SELECT * FROM SYS.DATABASES 
WHERE DATABASE_ID > 4 --Exclude sys databases.
AND  GETDATE() - CREATE_DATE > 30 --Return databases created more than 30 days ago.  
AND NAME LIKE  'CompanyName-HA%[_]%%[99999999]%%[_]%%[A-Z][a-z]%'
AND NAME NOT IN (  --Exclude important databases. 
'MyCompany-HA_DBName',
'MyCompany-HA_DBName2'
)) AS ListofDatabases

Select * from #ListofDbsToDrop 

I will place drop database loop here.

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You have a string. Assuming the parts before the date have no digits, you can get the date using this string manipulation:

convert(date, left(stuff(name, 1, patindex('%[0-9]%', name) - 1, ''), 8))

Then you can compare directly to something like dateadd(day, -30, getdate()) for your filtering.

If you have databases that don't follow this pattern, use try_convert():

try_convert(date, left(stuff(name, 1, patindex('%[0-9]%', name) - 1, ''), 8))

Upvotes: 1

Related Questions