Renaming database prefix to always be previous month

Question

I'm using SSMS 2012 and trying to figure out a way to write a query so that my database name will adjust to the second to most current month.

We have billing databases for every month that go like BillingMonth01206 and BillingMonth022016, as well as one called BillingMonthLM for "Last Month". The LM database is nice as we do not have to constantly change databases for last month's data, but I always need the one before that. So as it is now September, I need July billing (BillingMonth072016).

I have been doing a find and replace every month, but is there a way to automatically configure this? Maybe using a string or something?

Answer 1

 DECLARE  
       @Month varchar(2)
      ,@Year varchar(4)
      ,@SQL varchar(max)
      ,@DataBaseName varchar(50)

 SET @Month = CONVERT(varchar(2), DATEPART(MONTH, GETDATE()))
 SET @Year = DATEPART(YEAR, GETDATE())

 IF (LEN(@Month) = 1)
      SET @Month = '0' + @Month

 SET @DataBaseName = 'BillingMonth' + @Month + @Year

 SET @SQL = 'ALTER DATABASE BillingMonth072016  
             Modify Name = ' + @DataBaseName + '' 

 EXEC (@SQL)