Get business datetime in past or future

David Chase 681 Reputation points
2020-12-16T22:21:42.933+00:00

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;
4,388 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,166 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: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

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. David Chase 681 Reputation points
    2020-12-17T15:30:17.123+00:00

    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).

    1 person found this answer helpful.
    0 comments No comments

  2. Guoxiong 8,121 Reputation points
    2020-12-18T14:46:00.277+00:00

    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
    
    1 person found this answer helpful.