Get business datetime in past or future

Question

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;

Answer 1

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

Answer 2

The only column that matters is the column named dt and it holds just a smalldatetime with no time. I need to be able to get a datetime value returned that is xx hours before or after a passed datetime and hours. Example of Calendar values.

2020-12-16 00:00:00
2020-12-15 00:00:00
2020-12-14 00:00:00

I want to pass values like '2020-12-16 10:30:00' and hours of something like 20 or -20 and have it return (@result) a datetime that is 20 business hours from or prior to '2020-12-16 10:30:00' and the WHERE clause filters out only business days (isWeekday = 1 or isHoliday = 0).

Answer 3

Not sure if the format of the column [dt] is YYYY-MM-DD, i.e., 2020-12-16. Since my calendar table has [Date] with that format, it works. The trick part is that the calculation base depends on the hours passed in for the future or past.

DECLARE @dtfrom smalldatetime = '2020-12-16 14:30:00';
DECLARE @hours smallint = -20;
DECLARE @starttime time = '07:30:00';
DECLARE @endtime time = '16:00:00';

DECLARE @minutesPerDay int = DATEDIFF(MINUTE, @starttime, @endtime);
DECLARE @minutesToBeChangedInTotal int;
DECLARE @daysToBeChanged int;
DECLARE @minutesToBeChangedLeft int;
DECLARE @dtfromInDate date = CONVERT(varchar(10), @dtfrom, 121);

IF @hours >= 0 -- Calculate the date and time based on end date and time, in this case 2020-12-16 16:00:00
BEGIN
 SET @minutesToBeChangedInTotal = DATEDIFF(MINUTE, (CONVERT(varchar(10), @dtfrom, 121) + ' ' + CAST(@starttime AS varchar(8))), @dtfrom) + @hours * 60;
 SET @daysToBeChanged = @minutesToBeChangedInTotal / @minutesPerDay;
 SET @minutesToBeChangedLeft = @minutesToBeChangedInTotal % @minutesPerDay;

 SELECT DATEADD(MINUTE, @minutesToBeChangedLeft, (CONVERT(varchar(10), MIN([dt]), 121) + ' ' + CONVERT(varchar(10), @starttime))) 
 FROM [dbo].[Calendar] 
 WHERE [isWeekday] = 1 AND [isHoliday] = 0 AND [dt] > DATEADD(DAY, (@daysToBeChanged + 1), @dtfromInDate)
END
ELSE -- Calculate the date and time based on start date and time, in this case 2020-12-16 07:30:00
BEGIN
 SET @minutesToBeChangedInTotal = DATEDIFF(MINUTE, (CONVERT(varchar(10), @dtfrom, 121) + ' ' + CAST(@endtime AS varchar(8))), @dtfrom) + @hours * 60;
 SET @daysToBeChanged = @minutesToBeChangedInTotal / @minutesPerDay;
 SET @minutesToBeChangedLeft = @minutesToBeChangedInTotal % @minutesPerDay;

 SELECT DATEADD(MINUTE, @minutesToBeChangedLeft, (CONVERT(varchar(10), MAX([dt]), 121) + ' ' + CONVERT(varchar(10), @endtime))) 
 FROM [dbo].[Calendar] 
 WHERE [isWeekday] = 1 AND [isHoliday] = 0 AND [dt] < DATEADD(DAY, (@daysToBeChanged - 1), @dtfromInDate)
END
GO