如何在SQL中增加工作日的小时数？

Question

我看过很多SQL中添加工作日（即非周末）的示例，但我想添加小时。

例如；我想在日期上添加36小时，但不包括周六和周日。有人能帮助我吗？

CREATE FUNCTION AddWorkDays 
(    
    @WorkingDays As Int, 
    @StartDate AS DateTime 
) 
RETURNS DateTime 
AS
BEGIN
    DECLARE @Count AS Int
    DECLARE @i As Int
    DECLARE @NewDate As DateTime 
    SET @Count = 0 
    SET @i = 0 

    WHILE (@i < @WorkingDays) --runs through the number of days to add 
    BEGIN
-- increments the count variable 
        SELECT @Count = @Count + 1 
-- increments the i variable 
        SELECT @i = @i + 1 
-- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday 
-- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable 
           WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (1,7) 
            BEGIN
                SELECT @Count = @Count + 1 
            END
    END

-- adds the eventual count on to the Start Date and returns the new date 
    SELECT @NewDate = DATEADD(d,@Count,@StartDate) 
    RETURN @NewDate 
END
GO

- MesutAtasoy

---

工作日。指不包括假期（星期天、星期六）的日子。 - MesutAtasoy

你最好创建一个包含假期信息的日历表。例如，为每个可能的日期创建一行，并提供它是否是假期的信息。否则，代码将变得非常复杂。 - James Z

你可能想考虑使用星期几来确定要添加多少小时到日期中的case表达式。 - HABO

Answer 1

以下代码将在日期上加N个小时（不包括周六和周日）。 示例：

Declare @Date1 datetime = '2017-04-28'
Declare @Hours int  = 36

Select D=max(D)
 From  (
        Select D,HN=-1+Row_Number() over (Order by D)
         From  (Select D=DateAdd(HOUR,-1+Row_Number() Over (Order By (select null)),@Date1) From  master..spt_values n1 ) D
         Where DateName(WEEKDAY,D) not in ('Saturday','Sunday') 
       ) D1
 Where HN=@Hours

返回值

2017-05-01 12:00:00.000

- John Cappelletti

Answer 2

这是您在寻找的内容吗？

declare @num_hours int; 
    set @num_hours = 1; 

select dateadd(HOUR, @num_hours, getdate()) as time_with_hour;

- Biswabid

Answer 3

declare @num_hours int; 
set @num_hours = 5; 

select dateadd(HOUR, @num_hours, getdate()) as time_added, 
   getdate() as curr_date  

这个问题已经得到回答了。在这里查看。

- Ans Bilal