Select from dynamic table

I need to run a query in sql that depending on the month and year I need to call a different table.

For example:

If current_date is 31/08/2020, execute: select * from table_ago

If current_date is 01/09/2020, execute: select * from table_sep

Is it posible using a query in SQL Server?

Answer

You can use a stored procedure and if condition to make possible such a thing like following

create procedure spCheckTable
@date date   --create variable to enter date as parameter
as begin
    declare @toBeCheckDate date = (select top 1 date_column from BaseTable) --create variable to store date from table
    if @toBeCheckDate = @date
        begin
            select * from table_ago
        end
    else if @toBeCheckDate = @date
        begin
            select * from table_sep
        end
end

Now run the procedure like follow

execute spCheckTable '2020-09-02'