LIKE clause in T-SQL statement does not return value if compared with a variable?

I am trying to find all the backup tables I have just created with another script.

declare @bkup_tbls varchar(20) = '''%_' + LTRIM(RTRIM(CONVERT(CHAR(8), GETDATE(), 112  ))) + ''''

select @bkup_tbls  --'%_20210703' -- (1 row(s) affected)

select * 
from sys.objects 
where name like @bkup_tbls 
  and type = 'U' --(0 row(s) affected)

I have verified the tables exist in the sys.objects catalog view by the same value generated by the above declared variable.

select * 
from sys.objects 
where name like '%_20210703' 
  and type = 'U'  --(3 row(s) affected)

Am I missing anything? Any help would be much appreciated.

SQL Server version: Microsoft SQL Server 2016 (RTM)

Found related question, but that didn’t help either

Answer

If you print out the backup variable value, you will see that it has single quotes:

'%_20210703'

Those single quotes are part of the string, not delimiters. You don’t want them.

What you really want is:

declare @bkup_tbls varchar(20) = '%_' + LTRIM(RTRIM(CONVERT(CHAR(8), GETDATE(), 112  )))

which produces the string:

%_20210703

I suspect that you are confusing parameter substitution with dynamic SQL, which is why you think the delimiting single quotes are necessary.