國內最大的酷站演示中心!
--************
--在兩個日期范圍里所跨越那幾周返回如：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))
