[请教]计算工作日和工作时间算法

Question

--工作日：
declare @begindate datetime,@enddate datetime,@days int
set @begindate=getdate()-30
set @enddate=getdate()
set @days=0
while @begindate<=@endate
begin
if (datepart(DW,@begindate)=1) or (datepart(DW,@begindate)=7)
set @days=@days+1 
set @begindate=@begindate+1
end
--工作时只要用@days乘以工作时就行了！

Answer 1

create function WORKDAYDIFF(@fromDate datetime, @thruDate datetime) 
RETURNS int
as
begin
declare @ftDate datetime,@num int
set @num=0
set @ftDate=@fromDate
while @ftDate<=@thruDate
begin
if datepart(dw,@ftDate) not in (1,7)
set @num=@num + 1
set @ftDate=@ftDate + 1
end
return @num
end
go
select dbo.WORKDAYDIFF('2003-12-19','2003-12-21')

Answer 2

create function WORKDAYDIFF(@fromDate datetime, @thruDate datetime) 
RETURNS int
as
begin
declare @num int
set @num=0
while @fromDate<=@thruDate
begin
if datepart(dw,@fromDate) not in (1,7)
set @num=@num + 1
set @fromDate=@fromDate + 1
end
return @num
end
go
select dbo.WORKDAYDIFF('2003-12-19','2003-12-21')

Answer 3

create function WORKHOURDIFF(@fromDate datetime, @thruDate datetime) 
RETURNS int
as
begin
declare @hourNums int
select @hourNums=dbo.WORKDAYDIFF(@fromDate,@thruDate) * (24 - 15)
return @hourNums
end
goselect dbo.WORKHOURDIFF('2003-12-19','2003-12-21')

Answer 4

create function WORKDAYDIFF(@fromDate datetime, @thruDate datetime) 
RETURNS int
as
begin
return datediff(day,@fromDate,@thruDate)
     - datediff(week,@fromDate,@thruDate) 
       + case when datepart(weekday,@fromDate) > 1 then 0 else 1 end 
       - case when datepart(weekday,@thruDate) >= 1 then 0 else 1 end 
     - datediff(week,@fromDate,@thruDate) 
       + case when datepart(weekday,@fromDate) > 7 then 0 else 1 end 
       - case when datepart(weekday,@thruDate) >= 7 then 0 else 1 end 
endgocreate function WORKHOURDIFF(@fromDate datetime, @thruDate datetime) 
RETURNS int
as
begin
return dbo.WORKDAYDIFF(@fromDate,@thruDate) * 8
end参考:
请问有没有统计一个时间段星期几的个数的函数？急、、、、、
http://expert.csdn.net/Expert/TopicView1.asp?id=2585071

Answer 5

不好意思，可能我没有说清楚，我的fromDate和thruDate都是带时间的，而不仅仅是一个date。
比如：
select dbo.WORKDAYDIFF('2003-12-15 09:30:00','2003-12-16 09:15:00') = 0
select dbo.WORKDAYDIFF('2003-12-15 09:30:00','2003-12-16 09:45:00') = 1WORKDAYDIFF我可以参考playyuer(双规干部) 的算法，修改一下datediff(day,@fromDate,@thruDate)到datediff(hour, @fromDate, @thruDate) / 24就可以了。谢谢playyuer提供的算法，很简洁。但是麻烦的是WORKHOURDIFF （假设08：30 ~ 17:30为上班时间）:
select dbo.WORKHOURDIFF('2003-12-15 09:30:00','2003-12-16 09:30:00') = 9
select dbo.WORKHOURDIFF('2003-12-15 09:30:00','2003-12-16 07:00:00') = 8
还需要考虑开始时间和结束时间都可能为周末的问题，总共有16种排列组合，按照WORKDAYDIFF的方式（4种排列组合）去做的话，感觉代码很冗长，所以请教大家有没有更好的算法。

Answer 6

declare @fromDate datetime,@thruDate datetime
set @fromDate = '2001-04-01 08:31:00' 
set @thruDate = '2001-04-30 17:29:00'select  (datediff(day,@fromDate,@thruDate)
     - datediff(week,@fromDate,@thruDate) 
       + case when datepart(weekday,@fromDate) > 1 then 0 else 1 end 
       - case when datepart(weekday,@thruDate) >= 1 then 0 else 1 end 
     - datediff(week,@fromDate,@thruDate) 
       + case when datepart(weekday,@fromDate) > 7 then 0 else 1 end 
       - case when datepart(weekday,@thruDate) >= 7 then 0 else 1 end 
  -2) * 9 as 小时,datediff(minute,
 case when @fromDate >= dateadd(minute,8 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0)) 
           and  @fromDate <= dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))
           then @fromDate
      when @fromDate > dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))
           then dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0)) 
      else dateadd(minute,8 * 60 + 30 ,dateadd(day,datediff(day,0,@fromDate),0)) end
    ,dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@fromDate),0))) as 开始日期分钟数
,
datediff(minute
         ,dateadd(minute,8 * 60 + 30 ,dateadd(day,datediff(day,0,@thruDate),0))
         ,case when @thruDate <= dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0))
                    and  @thruDate >= dateadd(minute,8 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0))
                    then @thruDate
               when @thruDate < dateadd(minute,8 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0)) 
                    then dateadd(minute,8 * 60 + 30 ,dateadd(day,datediff(day,0,@thruDate),0))
               else dateadd(minute,17 * 60 + 30,dateadd(day,datediff(day,0,@thruDate),0)) end) as 结束日期分钟数

Answer 7

Hi playyuer(双规干部),
计算出开始日期分钟数, 结束日期分钟数以后，怎么用来计算工作时间间隔呢？
如果开始日期或者结束日期是周末，周日，这些时间又需要过滤掉，怎么处理比较方便呢？