0

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?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
TWaller
  • 81
  • 1
  • 7
  • 4
    You should load the data into a single reporting database/table and not much around with renaming databases. You'll end up with problems anyway, because you probably need to rename the underlying files as well. – Gordon Linoff Sep 12 '16 at 12:27
  • Database name shouldn't change. I can see where you might move off monthly snapshots for reporting, but the database should not be so transient. – duffymo Sep 12 '16 at 12:28
  • Thanks for the prompt responses. The underlying tables are the same in each database, so no tables names are affected. I just need to reference a different database each month. I cannot change the structure of the server, or the databases, so this is my only option. – TWaller Sep 12 '16 at 12:30
  • It is better to use YYYYMM format (201607). – i486 Sep 12 '16 at 14:02
  • You can automate find and replace. – Y.B. Sep 12 '16 at 14:27

1 Answers1

0
 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)
Thel3uilder
  • 26
  • 3
  • If you play around with this query you might be able to achieve what you want, but you will have to have Alter permissions on databases. – Thel3uilder Sep 13 '16 at 19:28