drop FUNCTION dbo.f_getdate
CREATE FUNCTION dbo.f_getdate(
@state datetime, --开始时间
@end datetime --结束时间
)RETURNS @re TABLE(T_Date datetime)
AS
begin
DECLARE @i int
set @i=datediff(dd,@state,@end)
while @i>0
begin
INSERT @re values(@state)
select @state=dateadd(dd,1,@state)
set @i=@i-1
end
INSERT @re values(@end)
RETURN
end
select * from dbo.f_getdate('1999-06-24','1999-07-06')
declare @BDate datetime,@EDate datetime
set @BDate ='1999-06-24'
set @EDate = '1999-07-06'
select dateadd(day,id-1,@BDate) from (
select id=a.id+b.id*10+c.id*100+d.id*1000+1 from
(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) b,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) c,(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) d
) aa
where id<=datediff(day,@BDate,@EDate)+1
order by id
declare @date datetime
set @date = '1999-06-24'
while (@date >='1999-06-24') and (@date<='1999-07-06')
begin
print convert(varchar(10),@date,21)
set @date = convert(varchar(10),dateadd(day,1,@date),21)
end
create function fnt_getDts(@tdt1 datetime,@tdt2 datetime)
returns @re table(dt datetime)
as
/*
功能:返回两个日期之间结果集
*/
begin
while @tdt1<=@tdt2
begin
--if datepart(dw,@tdt1)<>1 and datepart(dw,@tdt1)<>7 这里可过滤筛选日期。
insert into @re
select @tdt1
select @tdt1=dateadd(d,1,@tdt1)
end
return
end
go