DATEADD MINUTES Between Range MSSQL

Question

I am having some difficulty in trying to figure out something, lets say I have a date and time;

And I want to add 180 minutes to it so;

SELECT DATEADD(MINUTE,180,'2018-05-24 15:00')

This would give me answer of "2018-05-24 18:00" but I want to do it in a range so ADD the minutes if you are between 09:00 - 17:00 so something like this;

SELECT DATEADD(MINUTES,180,'2018-05-24 15:00') WHERE '2018-05-24 15:00' BETWEEN '2018-05-24 09:00' AND '2018-05-24 17:00'

So the answer to this would be "2018-05-25 10:00"

Answer 1

Was hard, but this should work for all your cases. This solution works for any amount of (positive) minutes and result will always be inside the parametrized hours, adding the corresponding amount of days.

DECLARE @RangeHourStart INT = 9
DECLARE @RangeHourEnd INT = 17

DECLARE @MinutesToAdd INT = 120

DECLARE @Date DATETIME = '2018-05-24 15:00'

SELECT
    FinalDate = CASE 
        WHEN -- When final hour exceeds the range hour
            DATEPART(HOUR, @Date) * 60 + 
            DATEPART(MINUTE, @Date) +
            @MinutesToAdd % ((@RangeHourEnd - @RangeHourStart) * 60) > @RangeHourEnd * 60 

        THEN
            DATEADD(HOUR, -1 * (@RangeHourStart - 1), 
                DATEADD(DAY, 1, 
                    DATEADD(MINUTE, @MinutesToAdd % ((@RangeHourEnd - @RangeHourStart) * 60), 
                        DATEADD(
                            DAY,
                            @MinutesToAdd / ((@RangeHourEnd - @RangeHourStart) * 60),
                            @Date)))) 

        ELSE
            DATEADD(MINUTE, @MinutesToAdd % ((@RangeHourEnd - @RangeHourStart) * 60), 
                DATEADD(
                    DAY,
                    @MinutesToAdd / ((@RangeHourEnd - @RangeHourStart) * 60),
                    @Date))
        END

I made it so you don't need to hard-code any value.