create table a (id int,name varchar(10))
insert into a select 1,'a'
insert into a select 2,'b'

declare @dt varchar(10),@oldname varchar(40),@newname varchar(40)
set @dt =replace(convert(varchar(10),getdate(),120),'-','')
set @newname='a_'+@dt
if exists(select 1 from sysobjects where [name]='a')
exec sp_rename 'a',@newname
else 
begin
set @oldname='a_'+replace(convert(varchar(10),getdate()-1,120),'-','')
if exists(select 1 from sysobjects where [name]=@oldname)
begin
exec sp_rename @oldname,@newname
exec('drop table '+@oldname)
end
end

declare @name varchar(10)
set @name='a_'+replace(convert(varchar(10),getdate(),120),'-','')
exec('select * from '+ @name)