Add a working hours based on business days

Question

I need to add a working hours for a given date and need to add dates based on working days.

Example:

DECLARE @Date datetime  = '2023-04-07 23:15:50.720';
DECLARE @AddHours int = 3;

IF the DATEADD(HOUR, @AddHours, @Date) falls under 'Saturday' or 'Sunday' we need to add it to next Working days between Monday to Friday based on @AddHours input

I tried with below code but it will not calculate for weekends

SELECT DATEADD(HOUR, @AddHours, @Date)

Example:

DECLARE @Date datetime  = '2023-04-07 23:15:50.720';
DECLARE @AddHours int = 3;

As per above @AddHours, the output supposed to be :

"2023-04-10 02:15:50.720"

Please provide me a solution as I'm a front end developer

Answer 1

You can use DATENAME or DATEPART to get if your new date is Sunday or Saturday, and adjust the adding value depending on it:

Declare @Date datetime  = '2023-04-07 23:15:50.720'

Declare @AddHours int = 3

SELECT CASE 
            WHEN DATENAME(WEEKDAY, DATEADD(HOUR, @AddHours, @Date)) = 'Saturday' THEN DATEADD(HOUR, @AddHours + 48, @Date)
            WHEN DATENAME(WEEKDAY, DATEADD(HOUR, @AddHours, @Date)) = 'Sunday' THEN DATEADD(HOUR, @AddHours + 24, @Date)
            ELSE DATEADD(HOUR, @AddHours, @Date)
       END

In the T-SQL you can create conditions using the case when block or IIF.