SQLServer生成時間範囲
前に似たような文章を書いたことがありますが、これは開発時に使ったものです.
if OBJECT_ID(N'tf_Data_TimeRange',N'FN') is not null
drop function tf_Data_TimeRange
go
create function tf_Data_TimeRange(
@startDate varchar(20), --
@endDate varchar(20), --
@dataType int -- 1: 2:
) returns @temp table(orderby int,MonitorTime varchar(20))
as
/********************************
--function:
--author:zhujt
--create date:2015-5-28 17:07:11
*********************************/
begin
if @dataType=1
begin
with temp(orderby,vdate) as
(select 1 orderby,convert(varchar(20),@startDate,120)
union all
select orderby+1, convert(varchar(20),dateadd(HOUR,1,vdate),120)
from temp
where vdate < @endDate
)
insert into @temp(orderby,MonitorTime)
select orderby,vdate from temp
OPTION (MAXRECURSION 0) --
end
else if @dataType=2
begin
set @endDate=convert(varchar(10),@endDate,120);
with temp(orderby,vdate) as
(select 1 orderby,convert(varchar(10),@startDate,120)
union all
select orderby+1, convert(varchar(10),dateadd(DD,1,vdate),120)
from temp
where vdate < @endDate
)
insert into @temp(orderby,MonitorTime)
select orderby,vdate from temp
OPTION (MAXRECURSION 0) --
end
return;
end