select convert(varchar(8),GETDATE(),112) --将时间格式转换 e.g. 2012-06-21 -->20120621

declare @datefrom date--时间格式
declare @dateto date--时间格式
declare @sql_qry varchar(max)
set @datefrom='2012-06-21'
set @dateto='2012-06-26'
print convert(varchar(8),@datefrom,112)
set @sql_qry=' select * from P'+convert(varchar(8),@datefrom,112)

while convert(varchar(8),@datefrom,112)<convert(varchar(8),@dateto,112)
begin
 set @datefrom=DATEADD(DD,1,@datefrom)
 set @sql_qry=@sql_qry+'
 union all 
 select * from P'+convert(varchar(8),@datefrom,112)
 

end
 print @sql_qry
 EXEC(@sql_qry)

--结果
 /*
 (1 row(s) affected)
20120621
 select * from P20120621
 union all 
 select * from P20120622
 union all 
 select * from P20120623
 union all 
 select * from P20120624
 union all 
 select * from P20120625
 union all 
 select * from P20120626
 */