如何在sqlserver 2008r2中计算天数之间的营业时间 [英] how to calculate business hours between days in sqlserver 2008r2

问题描述

如何计算sql server 2008中几天之间的营业时间？

请帮助让我知道非常紧急。

How to calculate business hours between days in sql server 2008?
Please help let me know very urgently.

推荐答案

SET DATEFIRST 7

declare @start as date
declare @end as date

set @start = '17/12/2013'
set @end = '17/01/2014'

declare @start_hr as time
declare @end_hr as time

set @start_hr = '9:00'
set @end_hr = '17:00'

declare @workdayscount as int

set @workdayscount = 0

while @start < @end
begin
	set @workdayscount = @workdayscount + case when datepart(weekday, @start) in (1, 2, 3, 4, 5) then 1 else 0 end
		
	set @start = dateadd(day, 1, @start)
end

select @workdayscount
select @workdayscount * datediff(hour, @start_hr, @end_hr)

1. SET DATEFIRST 7 - 将第一个工作日设置为Saunday。

2.清单（1,2,3,4,5）代表工作日 - 从周日到周四

1. SET DATEFIRST 7 - sets the first weekday to Saunday.
2. The list (1, 2, 3, 4, 5) represents the working days - form Sunday to Thursday

这篇关于如何在sqlserver 2008r2中计算天数之间的营业时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！