Hi @David Chase ,
Thanks for your update.
What is the expected output of result of 20 business hours prior to '2020-12-16 14:30:00' ?
'2020-12-14 11:30:00' or '2020-12-15 18:30:00' or else?
If it is '2020-12-14 11:30:00', you could refer below and check whether it is helpful to you since it is not very necessary to use cursor in your function.
DROP TABLE IF EXISTS #temp
DECLARE @dtfrom smalldatetime;
DECLARE @hours smallmoney;
DECLARE @date_string varchar(20);
DECLARE @result smalldatetime;
DECLARE @EndOfDay smalldatetime;
DECLARE @StartOfDay smalldatetime;
DECLARE @workinghours decimal(10,1);
DECLARE @n int
DECLARE @n1 decimal(10,1)
DECLARE @ID INT
SET @dtfrom = '2020-12-16 14:30:00';
SET @hours = -20;
SELECT identity(int,1,1) ID,[dt]
into #temp
FROM [dbo].[Calendar]
WHERE isWeekday = 1
AND isHoliday = 0
select @ID=ID FROM #temp WHERE CONVERT(DATE, @dtfrom)=CONVERT(DATE, dt)
SET @StartOfDay = CAST(FORMAT(@dtfrom,'yyyy-MM-dd') + ' 07:30:00' As smalldatetime);
SET @EndOfDay = CAST(FORMAT(@dtfrom,'yyyy-MM-dd') + ' 16:00:00' As smalldatetime);
SET @workinghours=(DATEDIFF(MINUTE,@StartOfDay,@EndOfDay))
if @hours<0
begin
SET @n=(@hours*60-(DATEDIFF(MINUTE,@dtfrom,@StartOfDay)))/@workinghours
SET @n1=(@hours*60-(DATEDIFF(MINUTE,@dtfrom,@StartOfDay)))%@workinghours
select @date_string = FORMAT(CONVERT(DATE, dt),'yyyy-MM-dd') from #temp where id=@ID+@n-1
set @result=CONVERT(datetime, @date_string + ' ' + convert(varchar(20),DATEADD(MINUTE,@n1,@EndOfDay),108))
end
else
begin
SET @n=(@hours*60-(DATEDIFF(MINUTE,@dtfrom,@EndOfDay)))/@workinghours
SET @n1=(@hours*60-(DATEDIFF(MINUTE,@dtfrom,@EndOfDay)))%@workinghours
select @date_string = FORMAT(CONVERT(DATE, dt),'yyyy-MM-dd') from #temp where id=@ID+@n+1
set @result=CONVERT(datetime, @date_string + ' ' + convert(varchar(20),DATEADD(MINUTE,@n1,@StartOfDay),108))
end
select @result
Output:
2020-12-14 11:30:00
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table