根据结束时间和开始时间统计任务的工作时长
1 CREATE FUNCTION [dbo].[Fn_CountWorkTime_Books_V2]
2 (@startDate datetime , @endDate datetime)
3 RETURNS float
4 as
5 BEGIN
6 if(@startDate> @endDate)
7 begin
8 return 0
9 end
10
11 declare @returnValue float -- 返回结果(天)
12 declare @returnInt Int -- 返回工作时长(秒)
13 declare @startDateInt int -- 开始时间时间范围
14 declare @endDateInt int -- 结束时间时间范围
15 declare @isHolidays int -- 是否为节假日,周六日
16 declare @startDateReturn int -- 非同一天,开始当天工作时长
17 declare @endDateReturn int -- 非同一天,结束当天工作时长
18 declare @middleReturn int -- 非同一天,中间工作时长
19
20 begin
21 -- 开始时间时间范围
22 if(@startDate < DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@startDate))))
23 begin
24 set @startDateInt = 2
25 set @startDateReturn = 8*60*60
26 end
27 else if(@startDate>= DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@startDate))) and @startDate<= DATEADD(HOUR,12,dbo.GETCURDAY(@startDate)))
28 begin
29 set @startDateInt = 2
30 set @startDateReturn = DATEDIFF(ss,@startDate,DATEADD(HOUR,12,dbo.GETCURDAY(@startDate))) + 4.5*60*60
31 end
32 else if(@startDate>DATEADD(HOUR,12,dbo.GETCURDAY(@startDate)) and @startDate< DATEADD(HOUR,13,dbo.GETCURDAY(@startDate)))
33 begin
34 set @startDateInt = 4
35 set @startDateReturn = 4.5*60*60
36 end
37 else if(@startDate>=DATEADD(HOUR,13,dbo.GETCURDAY(@startDate)) and @startDate<=DATEADD(n,30,DATEADD(HOUR,17,dbo.GETCURDAY(@startDate))) )
38 begin
39 set @startDateInt = 4
40 set @startDateReturn = DATEDIFF(ss,@startDate,DATEADD(n,30,DATEADD(HOUR,17,dbo.GETCURDAY(@startDate))))
41 end
42 else
43 begin
44 set @startDateInt = 4
45 set @startDateReturn = 0
46 end
47 -- 结束时间时间范围
48 if(@endDate < DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@endDate))))
49 begin
50 set @endDateInt = 2
51 set @endDateReturn = 0
52 end
53 else if(@endDate>= DATEADD(n,30,DATEADD(HOUR,8,dbo.GETCURDAY(@endDate))) and @endDate<= DATEADD(HOUR,12,dbo.GETCURDAY(@endDate)))
54 begin
55 set @endDateInt = 2
56 set @endDateReturn = DATEDIFF(ss,DATEADD(HOUR,8,dbo.GETCURDAY(@endDate)),@endDate)
57 end
58 else if(@endDate>DATEADD(HOUR,12,dbo.GETCURDAY(@endDate)) and @endDate< DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)))
59 begin
60 set @endDateInt = 4
61 set @endDateReturn = 3.5*60*60
62 end
63 else if(@endDate>=DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)) and @endDate<=DATEADD(n,30,DATEADD(HOUR,17,dbo.GETCURDAY(@endDate))) )
64 begin
65 set @endDateInt = 4
66 set @endDateReturn = DATEDIFF(ss,DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)),@endDate)+3.5*60*60
67 end
68 else
69 begin
70 set @endDateInt = 4
71 set @endDateReturn = 8*60*60
72 end
73 end
74
75 -- 判断是否为同一天
76 if(convert(varchar(10), @startDate,23) = convert(varchar(10), @endDate,23))
77 begin
78 --判断是否为节假日
79 select @isHolidays=count(1) from 节假日配置表 where 日期=convert(varchar(10), @startDate,23)
80 if(@isHolidays!=0)
81 begin
82 set @returnInt = 0
83 end
84 else
85 begin
86 if(@startDateInt = 2 and @startDateInt=@endDateInt)
87 begin
88 -- 结束-开始
89 set @returnInt = DATEDIFF(ss,@startDate,@endDate)
90 end
91 else if(@startDateInt = 2 and @endDateInt = 4)
92 begin
93 -- (12点-开始)+(结束-13)
94 set @returnInt = DATEDIFF(ss,@startDate,DATEADD(HOUR,12,dbo.GETCURDAY(@endDate))) + DATEDIFF(ss,DATEADD(HOUR,13,dbo.GETCURDAY(@endDate)),@endDate)
95 end
96 else
97 begin
98 -- 结束-开始
99 set @returnInt = DATEDIFF(ss,@startDate,@endDate)
100 end
101 end
102 end
103 else
104 begin
105 -- 不为同一天,工作时长=开始当天工作时长+结束当天工作时长+中间工作时长
106 -- 中间工作时长
107 declare @totalDays int
108 declare @totalUnWorkDay int
109 set @totalDays = DATEDIFF(day,@startDate,@endDate)
110 if(@totalDays > 1)
111 begin
112 select @totalUnWorkDay=count(1) from 节假日配置表 where 日期>@startDate and 日期<@endDate
113 set @middleReturn = (@totalDays-1-@totalUnWorkDay)*8*60*60
114 end
115 else
116 begin
117 set @middleReturn = 0
118 end
119
120 set @returnInt = @startDateReturn+@endDateReturn+@middleReturn
121 end
122
123 set @returnValue = cast(cast(@returnInt as float)/cast(28800 as float) as float(4))
124 return @returnValue
125 end
ps: 网上实在是搜不到8小时制统计工作时长的函数,特此将自己的解决思路分享出来,希望能帮助到其他人. 如果你和更好的解决思路 ,欢迎讨论.
