SQL Server : how to always query a table that has the current date in it's name

Question

I am on a database that always creates a report table with a name of Table_Name_YYYYMMDD.

I would like to create a query that using a subquery like:

Select convert(varchar, getdate(), 112)

Could allow me to always get the results from table with the current date in the name.

The user has read-only access to the database.

I did try the following:

Declare @Tname varchar(50); 
Set @Tname = 'table_name_' + (Select convert(varchar, getdate(), 112));

Declare @Q varchar(200);
Set @Q = 'Select * From ' + @Tname;

Exec @Q;

But I get an error :

Must declare the scalar variable "@Q".

Thanks!

Answer 1

Your question is not very clear... You seem to know, that the approach with value-named table names is a bad one. But you told us, that you have to stick to this third-party tool. Well, shit happens ;-)

Your code should work, but assumably you don't show everything and/or you tried to shorten it for brevity (which is great!), but - maybe - you shortened the error away...

Try this:

USE master;
GO
CREATE DATABASE testTbWithDate; --Some extra database to create tables
GO
USE testTbWithDate;
GO
CREATE TABLE SomeName_20190701(ID INT,SomeValue VARCHAR(100));
GO
CREATE TABLE SomeName_20190630(ID INT,SomeValue VARCHAR(100));
GO
--test values
INSERT INTO SomeName_20190630 VALUES(1,'this is 30th of June'); 
INSERT INTO SomeName_20190701 VALUES(1,'this is 1st of July');
GO

--Here starts the query

--You should always use (n)varchar together with a length parameter
--And you should not keep your length to small...
Declare @Tname varchar(50) =  --<-- 50 might be not enough...
   'SomeName_' + (Select convert(varchar(8), {d'2019-07-01'}, 112)); --I added "(8)"


Declare @Q varchar(max); --This was pretty small, I use "(max)"
Set @Q = 'Select * From ' + QUOTENAME(@Tname); --I use QOUTENAME to avoid invalid names and help (a little bit) against SQL injection

PRINT @Q; --always print out your command. Now you can copy and paste it into a query window and check the syntax.
Exec(@Q); --the execution

--Clean-Up

GO
USE master;
GO
DROP DATABASE testTbWithDate;