MSSQL游标对日期数据库的简单操作

declare temp_cursor CURSOR for

 

select distinct [month]=convert(varchar(6),dateadd(dd,number,@begintime),112) from master..spt_values where type='p'  and number <= datediff(dd,@begintime,@endtime) 

open temp_cursor

declare @ym varchar(20)

fetch next from temp_cursor into @ym

while @@fetch_status=0

begin

if @sqltxt!=''

begin

set @sqltxt+=' union all ';

end

declare @tablename varchar(50)

declare @dbname varchar(50)

set @dbname='HBYY_JZ_'+@ym;

set @tablename='tb_lampblackpoint'

 

if object_id(N''+@dbname+'.dbo.'+@tablename+'',N'U') is not null

begin

set @sqltxt+=' select lampblackid,day from (select lampblackid,(LEFT(createtime,10)) day from '+@dbname+'.dbo.'+@tablename+' where createtime>='''+cast(@begintime as varchar)+''' and createtime<='''+cast(@endtime as varchar) +''' ) a group by lampblackid,day'

    end

fetch next from temp_cursor into @ym

end

close temp_cursor;

deallocate temp_cursor;



© 2016-2021 阿尔佛 aerfo.com | 豫ICP备17044542号 | 豫公网安备 41010602000172