create proc sp_wsp @starttime datetime as select 总工作日=sum(px) from (select px=case when datepart(dw,dateadd(dd,number,@starttime)) in(7,1) then 0 else 1 end from master..spt_values where type='p' and number between 1 and datediff(dd,@starttime,getdate()))a go exec sp_wsp '2010-07-10'/*结果: 总工作日 17 */
-------------------------------------------------- -- 功能:返回两个日期之间的工作日数(星期一至五)。 -------------------------------------------------- create function fn_workdaydiff(@date1 datetime, @date2 datetime) returns int asbegindeclare @days smallint, @weekday tinyint, @mod tinyint set @days = datediff(day,@date1,@date2) if @days < 0 set @weekday = (@@datefirst+datepart(weekday,@date2)-1)%7 else set @weekday = (@@datefirst+datepart(weekday,@date1)-1)%7 select @days = abs(@days)+1, @mod = @days%7, @days = @days/7*5 if @mod > 0 set @days = @days+@mod-(7-@weekday)/7-(@mod+@weekday)/7-(@mod+@weekday)/8 return (@days)end go-- 调用 select dbo.fn_workdaydiff('2010-07-10', getdate()) -- 17
declare @starttime datetime set @starttime='2010-7-20' select COUNT(*) from master..spt_values where type='p' and number between 1 and datediff(dd,@starttime,getdate()) and datepart(dw,dateadd(dd,number,@starttime)) between 2 and 6
create proc sp_wsp
@starttime datetime
as
select 总工作日=sum(px) from (select px=case when datepart(dw,dateadd(dd,number,@starttime)) in(7,1) then 0 else 1 end
from master..spt_values where type='p' and number between 1 and
datediff(dd,@starttime,getdate()))a
go
exec sp_wsp '2010-07-10'/*结果:
总工作日
17
*/
-- 功能:返回两个日期之间的工作日数(星期一至五)。
--------------------------------------------------
create function fn_workdaydiff(@date1 datetime, @date2 datetime)
returns int
asbegindeclare @days smallint, @weekday tinyint, @mod tinyint
set @days = datediff(day,@date1,@date2)
if @days < 0 set @weekday = (@@datefirst+datepart(weekday,@date2)-1)%7
else set @weekday = (@@datefirst+datepart(weekday,@date1)-1)%7
select @days = abs(@days)+1, @mod = @days%7, @days = @days/7*5
if @mod > 0 set @days = @days+@mod-(7-@weekday)/7-(@mod+@weekday)/7-(@mod+@weekday)/8
return (@days)end
go-- 调用
select dbo.fn_workdaydiff('2010-07-10', getdate()) -- 17
declare @starttime datetime
set @starttime='2010-7-20'
select COUNT(*)
from master..spt_values where type='p' and number between 1 and datediff(dd,@starttime,getdate())
and datepart(dw,dateadd(dd,number,@starttime)) between 2 and 6
-----------
10(1 行受影响)