I would have used the concat function SQL Server 2012 or better, eliminate the cast by letting concat handle the char(10) cast and use sysdatetime() instead of Getdate().


declare @DBName as varchar(20);
set @DBName = 'Branch1';
declare @Date as date;
set @Date = sysdatetime() --not Getdate();
declare @SQL as varchar(2000);

select @SQL
  = 'SELECT COUNT(1) ' + 'FROM [' + @DBName + '].[dbo].[CommonTable] ' + 'WHERE [InsertDate] = '''
  + cast(@Date as char(10)) + '''';
print @SQL;

select @SQL                                                                                                                                                
  = concat('SELECT COUNT(1) FROM [', @DBName, '].[dbo].[CommonTable] WHERE [InsertDate] = ', quotename(@Date, ''''));

print @SQL;
EXEC (@SQL)