--我用的方法比较本
--下面的函数GetDateFromWeekDay也是以前别人写的
/*
-- 得到一周的某一天
*/
create function GetDateFromWeekDay(@Year integer,@Week integer,@WeekDay integer)
RETURNS datetime
as
begin
return dateadd(day,-datepart(weekday,dateadd(week,@Week-1,dateadd(year,@Year -1900,0))) + @WeekDay,dateadd(week,@Week-1,dateadd(year,@Year -1900,0)))
end
-------------------------------------------
DECLARE @Week as int
DECLARE @DT1 as smalldatetime
DECLARE @DT2 as smalldatetime
set @Week=datepart(ww,@DT)
set @DT1= dbo.GetDateFromWeekDay(datepart(yy,@DT),@week,1)
set @DT2= dbo.GetDateFromWeekDay(datepart(yy,@DT),@week,7)
--@DT1是一周的开始时间,@DT2是一周的结束时间
--我这样修改后,反正我的系统是可以用了
return
(select dateadd(day
,case when (@@datefirst + datepart(weekday,@date)) % 7 = 0 --周六
then
case when @ between 1 and 6
then @ - 6
else 1
end
when (@@datefirst + datepart(weekday,@date)) % 7 = 1 --周日(七)
then
case when @ between 1 and 6
then @ - 7
else 0
end
when (@@datefirst + datepart(weekday,@date)) % 7 between 2 and 6 --周一至周五
then
case when @ between 1 and 6
then @ + 1 - (@@datefirst + datepart(weekday,@date)) % 7
else 8 - (@@datefirst + datepart(weekday,@date)) % 7
end
end
,@date))
end
go
2.方法2
create function udf_WeekDiff(@BeginDate datetime,@EndDate datetime)
returns integer
begin
return (
select datediff(week,@BeginDate,@EndDate) -- + 1
+ case when (@@datefirst + datepart(weekday,@BeginDate)) % 7 = 1 then 1 else 0 end
- case when (@@datefirst + datepart(weekday,@EndDate)) % 7 = 1 then 1 else 0 end
)
end
go
declare @b datetime,@e datetime
set @b = '2004-12-12'
set @e = '2004-12-20'
select dbo.udf_WeekDiff(@b,@e) + 1 as 跨周数
,dbo.udf_WeekDiff(@b,@e) as 间隔周数
,datediff(week,@b,@e) as [datediff(week,startdate,enddate)]