国内最大的酷站演示中心!
--************
--在两个日期范围里所跨越那几周返回如：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 @[email protected]
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 @[email protected]
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 @[email protected] - 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))
