英文:
Add a working hours based on business days
问题
需要为指定日期添加工作小时，以及根据工作日添加日期。
示例：
DECLARE @Date datetime = '2023-04-07 23:15:50.720';
DECLARE @AddHours int = 3;
如果DATEADD(HOUR, @AddHours, @Date) 落在 '星期六' 或 '星期天'，我们需要将它添加到下一个工作日，即从星期一到星期五，基于 @AddHours 输入。
我尝试了下面的代码，但它不会计算周末：
SELECT DATEADD(HOUR, @AddHours, @Date)
示例：
DECLARE @Date datetime = '2023-04-07 23:15:50.720';
DECLARE @AddHours int = 3;
根据上述 @AddHours，输出应该是：
"2023-04-10 02:15:50.720"
请为我提供一个解决方案，因为我是前端开发人员。
英文:
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
答案1
得分: 0
你可以使用DATENAME或DATEPART来获取你的新日期是星期日还是星期六，并根据这一情况调整添加的值：
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
在T-SQL中，你可以使用case when块或IIF来创建条件。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论