如何在sqlserver 2008r2中计算天数之间的营业时间

问题描述

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

请帮助。

How to calculate business hours between days in sql server 2008?
Please help.

推荐答案

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中计算天数之间的营业时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！