IDC资讯中心
–************
–在两个日期范围里所跨越那几周返回如：1，2表是第一周和第二周，
declare @aa table (date datetime,weekdays int )
declare @i int
set @i=datediff(day,@bdate,@edate)
while(@i>=0)
begin
insert @aa
values (dateadd(day,@i,@bdate),datepart(week,dateadd(day,@i,@bdate)))
set @i=@i-1
end
select weekdays
into #week
from @aa group by weekdays
–************
–在日期范围里减去周六、周日的天数
create function a (@sdate datetime ,@edate datetime)
returns int
as
begin
declare @aa table (date datetime)
declare @i int
set @i=datediff(day,@sdate,@edate)
while(@i>=0)
begin
insert @aa
values (dateadd(day,@i,@sdate))
set @i=@i-1
end
select @i= count(*) from @aa where datepart(weekday,date) not in (1,7)
return @i
end
–如：select dbo.a(2004-10-01,2004-10-11)
–返回结果为7
–***********
–输入第几周得到此周的开始、结束日期
declare @firstdayofyear datetime–年頭
declare @firstdayweekofyear datetime –第一周的第一天
declare @bdate datetime
declare @edate datetime
select @firstdayofyear= dateadd(yy,datediff(yy,0,getdate()),0)
select @firstdayweekofyear=@firstdayofyear – datepart(dw, @firstdayofyear)+1
select @edate=dateadd(ww,@week,@firstdayweekofyear-1 )
select @bdate= dateadd(ww,-1,dateadd(dd,1,@edate) )
set @bdate =convert(datetime, convert(char(10),@bdate,101))
set @edate =convert(datetime, convert(char(10),@edate,101))
