24h/7d Time Periods TO 10h/5d Time Periods

I have to do alot of date calculations. For some calculations, I can use the datediff or dateadd function to get a Time Period between 2 dates.

Now for some dates I need to calculate the time between 2 dates BUT:

a week = 5 days starting from Monday to Friday a day starts at 8AM and ends at 6PM (so a day is 10 hours)

You can probably calculate this manually but what about summer to winter hour and the month Februari when it has 29 days etc.

So I was thinking ... is it possible to calculate the Time Period for weeks with 24h a day / 7 days AND then transform that Time Period to a time period for weeks with 10h a day / 5 days ?

If anyone has an idea to solve this, either with functions or an other way, please let me know! Thanks

For our internal help desk application I created the following user-defined function to calculate the hours between 8am and 5pm Monday - Friday. There are obvious holes, but it fell within the management guidelines for their reporting:

CREATE FUNCTION [dbo].fnGetWorkHours
(@.start_dt datetime=null, @.end_dt datetime=null, @.co_user_id int=null)
RETURNS int

AS
-- Returns the number of weekday hours between the supplied dates
BEGIN
declare @.d1 datetime, @.d2 datetime, @.d1a datetime, @.d2a datetime, @.hours int, @.offset int

set @.offset = 0
if @.co_user_id is not null
begin
select @.offset = l.timezone_offset
from [HelpDesk].[dbo].[Company_User] c INNER JOIN [HelpDesk].[dbo].[Location_Def] l
on c.[loc_id] = l.[loc_id]
where c.co_user_id = @.co_user_id
end

set @.d1 = dateadd(hour, @.offset, @.start_dt)
set @.d2 = dateadd(hour, @.offset, @.end_dt)

if convert(char(10),@.d1,101) = convert(char(10),@.d2,101)
BEGIN
SELECT @.hours = DATEDIFF(hour, @.d1, @.d2)
END
ELSE
BEGIN
SET @.d1 = CASE WHEN @.d1 > convert(char(10),@.d1,101) + ' 17:00' THEN convert(char(10),dateadd(day,1,@.d1),101) + ' 08:00'
ELSE @.d1 END
SET @.d2 = CASE WHEN @.d2 < convert(char(10),@.d2,101) + ' 08:00' THEN convert(char(10),@.d2,101) + ' 08:00'
ELSE @.d2 END
SET @.d1 = CASE WHEN DATEPART(weekday,@.d1+@.@.DATEFIRST)=1 THEN convert(char(10),dateadd(day,1,@.d1),101) + ' 08:00'
WHEN DATEPART(weekday,@.d1+@.@.DATEFIRST)=7 THEN convert(char(10),dateadd(day,2,@.d1),101) + ' 08:00'
ELSE @.d1 END
SET @.d2 = CASE WHEN DATEPART(weekday,@.d2+@.@.DATEFIRST)=1 THEN convert(char(10),dateadd(day,-2,@.d2),101) + ' 17:00'
WHEN DATEPART(weekday,@.d2+@.@.DATEFIRST)=7 THEN convert(char(10),dateadd(day,-1,@.d2),101) + ' 17:00'
ELSE @.d2 END
set @.d1a = dateadd(day,1,@.d1)
set @.d2a = dateadd(day,-1,@.d2)
SELECT @.hours =
CASE WHEN DATEDIFF(day, @.d1, @.d2) > 1 THEN
(DATEDIFF(day, @.d1a, @.d2a) + 1 - (2 * DATEDIFF(week, @.d1, @.d2))) * 8
ELSE 0 END +
CASE WHEN convert(char(10),@.d1,101) = convert(char(10),@.d2,101)
THEN DATEDIFF(hour, @.d1, @.d2)
WHEN convert(char(10),@.d1,101) < convert(char(10),@.d2,101)
THEN DATEDIFF(hour, @.d1, convert(char(10),@.d1,101) + ' 17:00')
ELSE 0 END +
CASE WHEN DATEDIFF(day, @.d1, @.d2) > 0 THEN
DATEDIFF(hour, convert(char(10),@.d2,101) + ' 08:00', @.d2)
ELSE 0 END
END
RETURN(@.hours)
END
go

|||Best is to build a calendar table with the possible dates, columns that track working days, holidays, time adjustments etc. You can then easily write queries to answer your questions. It is possible to write scalar UDFs but the logic for those will often be complicated and hard to use if you want to support different types of calculations easily.