Defining SQL Server table name from date

Question

My SQL Server database has tables named after the load date in this format

## FROM [GL_202206].[dbo].[GL_ACCOUNT_20220602].

I have done a query that can generate a similar sting as the table name as below:

SELECT 
    CONCAT('[GL_', YEAR(GETDATE()), FORMAT (GETDATE(), 'MM'),
           '].[dbo].[GL_ACCOUNT_', CONVERT(CHAR(8), GETDATE(), 112), ']')

How can I get the string to make the table name in the query?

Say

SELECT 
    ACCOUNTID, ACCOUNTNAME 
FROM 
    [GL_202206].[dbo].[GL_ACCOUNT_20220602]

But honestly, when you need table names like that is smells like a bad design to me — GuidoG, Jun 03 '22 at 14:22
I know you might not have any say here, but this is an awful way to define these tables. There should be **ONE TABLE**, with these date values in a column as the first part of a composite primary key. — Joel Coehoorn, Jun 03 '22 at 14:50
https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable — Stewart, Jun 03 '22 at 14:56

score 0 · Accepted Answer · answered Jun 04 '22 at 21:40

Your SQL is correct, you only need to execute it.

declare @sql nvarchar(max) = null;
SELECT @sql = 'select ACCOUNTID, ACCOUNTNAME from ' +
    CONCAT('[GL_', YEAR(GETDATE()), FORMAT (GETDATE(), 'MM'),
           '].[dbo].[GL_ACCOUNT_', CONVERT(CHAR(8), GETDATE(), 112), ']');
exec (@sql)

Defining SQL Server table name from date

1 Answers1