SQL Server Add a working hours based on business days

jfewjypa 于 2023-04-04 发布在其他

关注(0)|答案(1)|浏览(100)

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

sql-server

来源：https://stackoverflow.com/questions/75926908/add-a-working-hours-based-on-business-days

1条答案

按热度按时间

erhoui1w1#

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 .

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Add a working hours based on business days

1条答案

相关问题

热门标签

最新问答