-
MelissaMa-MSFT 24,131 Reputation points
2020-12-18T08:44:12.97+00:00 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:00Best 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
Get business datetime in past or future

David Chase
681
Reputation points
I am trying to build an SQL function that returns a future or past datetime value in business days from a passed datetime and hours. Below is the schema of our Calendar table and below that is what I have so far in my SQL. I am stuck on formulating the ELSE condition. Any direction would be helpful.
CREATE TABLE [dbo].[Calendar](
[dt] [smalldatetime] NOT NULL,
[isWeekday] [bit] NULL,
[isHoliday] [bit] NULL,
[Y] [smallint] NULL,
[FY] [smallint] NULL,
[Q] [tinyint] NULL,
[M] [tinyint] NULL,
[D] [tinyint] NULL,
[DW] [tinyint] NULL,
[monthname] [varchar](9) NULL,
[dayname] [varchar](9) NULL,
[W] [tinyint] NULL,
[UTCOffset] [tinyint] NULL,
PRIMARY KEY CLUSTERED
(
[dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @dtfrom smalldatetime;
DECLARE @hours smallmoney;
DECLARE @minutes int;
DECLARE @dt smalldatetime;
DECLARE @dttime smalldatetime;
DECLARE @hoursremain smallmoney;
DECLARE @result smalldatetime;
DECLARE @EndOfDay smalldatetime;
DECLARE @StartOfDay smalldatetime;
DECLARE @Noon smalldatetime;
SET @dtfrom = '2020-12-16 14:30:00';
SET @hours = -20;
SET @hoursremain = @hours;
DECLARE day_cursor CURSOR FOR
SELECT TOP (20) [dt]
FROM [Marshall].[dbo].[Calendar]
WHERE dt < @dtfrom
AND isWeekday = 1
AND isHoliday = 0
ORDER BY dt DESC;
OPEN day_cursor;
FETCH NEXT FROM day_cursor
INTO @dt;
WHILE @hoursremain <> 0
BEGIN
SET @StartOfDay = CAST(FORMAT(@dt,'yyyy-MM-dd') + ' 07:30:00' As smalldatetime);
SET @Noon = CAST(FORMAT(@dt,'yyyy-MM-dd') + ' 12:00:00' As smalldatetime);
SET @EndOfDay = CAST(FORMAT(@dt,'yyyy-MM-dd') + ' 16:00:00' As smalldatetime);
IF DATEADD(DAY,DATEDIFF(d,0,@dtfrom),0) = DATEADD(DAY,DATEDIFF(d,0,@dt),0)
BEGIN
--from date same as current calendar date
SET @minutes = DATEDIFF(MINUTE, @dtfrom, @EndOfDay)
SET @result = DATEADD(MINUTE, @minutes * -1, @dtfrom);
SET @hoursremain = 0;
END
ELSE
BEGIN
SET @result = GETDATE();
SET @hoursremain = 0;
END
FETCH NEXT FROM day_cursor
INTO @dt;
END;
CLOSE day_cursor;
DEALLOCATE day_cursor;
{count} votes
Accepted answer