首頁>Program>source

如何計算两个日期之間的营業時間? 例如,我们有两个日期. 2010年1月1日15:00和2010年4月1日12:00 我们在工作日的工作時間為09:00至17:00 如何使用sql計算工作時間?

最新回復
  • 5月前
    1 #

    對於SQL 2005固定和修改的Baran答案

    SQL 2008及更高版本:

    -- =============================================
    -- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
    -- Create date: 14.03.2011
    -- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
    -- =============================================
    CREATE FUNCTION [dbo].[WorkTime] 
    (
        @StartDate DATETIME,
        @FinishDate DATETIME
    )
    RETURNS BIGINT
    AS
    BEGIN
        DECLARE @Temp BIGINT
        SET @Temp=0
        DECLARE @FirstDay DATE
        SET @FirstDay = CONVERT(DATE, @StartDate, 112)
        DECLARE @LastDay DATE
        SET @LastDay = CONVERT(DATE, @FinishDate, 112)
        DECLARE @StartTime TIME
        SET @StartTime = CONVERT(TIME, @StartDate)
        DECLARE @FinishTime TIME
        SET @FinishTime = CONVERT(TIME, @FinishDate)
        DECLARE @WorkStart TIME
        SET @WorkStart = '09:00'
        DECLARE @WorkFinish TIME
        SET @WorkFinish = '17:00'
        DECLARE @DailyWorkTime BIGINT
        SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
        IF (@StartTime<@WorkStart)
        BEGIN
            SET @StartTime = @WorkStart
        END
        IF (@FinishTime>@WorkFinish)
        BEGIN
            SET @[email protected]
        END
        IF (@FinishTime<@WorkStart)
        BEGIN
            SET @[email protected]
        END
        IF (@StartTime>@WorkFinish)
        BEGIN
            SET @StartTime = @WorkFinish
        END
        DECLARE @CurrentDate DATE
        SET @CurrentDate = @FirstDay
        DECLARE @LastDate DATE
        SET @LastDate = @LastDay
        WHILE(@CurrentDate<[email protected])
        BEGIN       
            IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
            BEGIN
                IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = @Temp + @DailyWorkTime
                END
                --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
                ELSE IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
                END
                ELSE IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
                END
                --IF it starts and finishes in the same date
                ELSE IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
                END
            END
            SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
        END
        -- Return the result of the function
        IF @Temp<0
        BEGIN
            SET @Temp=0
        END
        RETURN @Temp
    END
    

    SQL 2005及以下版本:

    -- =============================================
    -- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
    -- Create date: 14.03.2011
    -- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
    -- =============================================
    CREATE FUNCTION [dbo].[WorkTime] 
    (
        @StartDate DATETIME,
        @FinishDate DATETIME
    )
    RETURNS BIGINT
    AS
    BEGIN
        DECLARE @Temp BIGINT
        SET @Temp=0
        DECLARE @FirstDay DATETIME
        SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))
        DECLARE @LastDay DATETIME
        SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))
        DECLARE @StartTime DATETIME
        SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
        DECLARE @FinishTime DATETIME
        SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)
        DECLARE @WorkStart DATETIME
        SET @WorkStart = CONVERT(DATETIME, '09:00', 8)
        DECLARE @WorkFinish DATETIME
        SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)
        DECLARE @DailyWorkTime BIGINT
        SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
        IF (@StartTime<@WorkStart)
        BEGIN
            SET @StartTime = @WorkStart
        END
        IF (@FinishTime>@WorkFinish)
        BEGIN
            SET @[email protected]
        END
        IF (@FinishTime<@WorkStart)
        BEGIN
            SET @[email protected]
        END
        IF (@StartTime>@WorkFinish)
        BEGIN
            SET @StartTime = @WorkFinish
        END
        DECLARE @CurrentDate DATETIME
        SET @CurrentDate = @FirstDay
        DECLARE @LastDate DATETIME
        SET @LastDate = @LastDay
        WHILE(@CurrentDate<[email protected])
        BEGIN       
            IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
            BEGIN
                IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = @Temp + @DailyWorkTime
                END
                --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
                ELSE IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
                END
                ELSE IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
                END
                --IF it starts and finishes in the same date
                ELSE IF (@[email protected]) AND (@[email protected])
                BEGIN
                    SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
                END
            END
            SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
        END
        -- Return the result of the function
        IF @Temp<0
        BEGIN
            SET @Temp=0
        END
        RETURN @Temp
    END
    

  • 5月前
    2 #

    我知道這是一篇很老的文章,但這是我最近編寫的用於計算任意两个事件之間的营業時間/分钟數的函式.它還考虑了必须在表中定義的任何假​​期。

    该函式以分钟為單位返迴時間間隔-您可以除以60得到所需的小時數。

    這已在SQL Server 2008上进行了測試。希望它能對某人有所帮助。

    Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
    AS
    Begin
        Declare @WorkMin int = 0   -- Initialize counter
        Declare @Reverse bit       -- Flag to hold if direction is reverse
        Declare @StartHour int = 9   -- Start of business hours (can be supplied as an argument if needed)
        Declare @EndHour int = 17    -- End of business hours (can be supplied as an argument if needed)
        Declare @Holidays Table (HDate DateTime)   --  Table variable to hold holidayes
        -- If dates are in reverse order, switch them and set flag
        If @StartDate>@EndDate 
        Begin
            Declare @TempDate [email protected]
            Set @[email protected]
            Set @[email protected]
            Set @Reverse=1
        End
        Else Set @Reverse = 0
        -- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
        Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where [email protected] and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)
        If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate))  -- If Start time is less than start hour, set it to start hour
        If DatePart(HH, @StartDate)>[email protected]+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
        If DatePart(HH, @EndDate)>[email protected]+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
        If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day
        If @StartDate>@EndDate Return 0
        -- If Start and End is on same day
        If DateDiff(Day,@StartDate,@EndDate) <= 0
        Begin
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If day is between sunday and saturday
                If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If day is not a holiday
                    If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
                Else Return 0
            Else Return 0
        End
        Else Begin
            Declare @Partial int=1   -- Set partial day flag
            While DateDiff(Day,@StartDate,@EndDate) > 0   -- While start and end days are different
            Begin
                If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    --  If this is a weekday
                Begin
                    If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If this is not a holiday
                    Begin
                        If @Partial=1  -- If this is the first iteration, calculate partial time
                        Begin 
                            Set @[email protected] + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
                            Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) 
                            Set @Partial=0 
                        End
                        Else Begin      -- If this is a full day, add full minutes
                            Set @[email protected] + (@[email protected])*60        
                            Set @StartDate = DATEADD(DD,1,@StartDate)
                        End
                    End
                    Else Set @StartDate = DATEADD(DD,1,@StartDate)  
                End
                Else Set @StartDate = DATEADD(DD,1,@StartDate)
            End
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If last day is a weekday
                If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0   -- And it is not a holiday
                    If @Partial=0 Set @[email protected] + DATEDIFF(MI, @StartDate, @EndDate) Else Set @[email protected] + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
        End 
        If @Reverse=1 Set @[email protected]
        Return @WorkMin
    End
    

  • 5月前
    3 #

    第一步是計算工作日,如下面的指令碼所示:

    DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2), @DateFrom DATETIME, @DateTo DATETIME;
    SET @DateFrom = '2017-06-05 11:19:11.287';
    SET @DateTo = '2017-06-07 09:53:14.750';
    SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
        -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
       -CASE
                                        WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
                                        THEN 1
                                        ELSE 0
                                    END+CASE
                                            WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
                                            THEN 1
                                            ELSE 0
                                        END;
    

    第二步涉及获得两个日期之間的秒數差異,並通過除以3600.0將该差異轉換為小時數,如以下指令碼所示:

    SET @TotalTimeDiff =
    (
        SELECT DATEDIFF(SECOND,
                       (
                           SELECT CONVERT(TIME, @DateFrom)
                       ),
                       (
                           SELECT CONVERT(TIME, @DateTo)
                       )) / 3600.0
    );
    

    最後一部分涉及將上述第一步的輸出乘以24(一天的总小時數),然後將其添加到第二步的輸出中:

    SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff;
    

    最後,可用於建立使用者定義函式以計算工作時間的完整指令碼如下所示:

    CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours]
    (
        @DateFrom Datetime,
        @DateTo Datetime
    )
    RETURNS DECIMAL(18,2)
    AS
    BEGIN
    DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)
    SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
        -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
       -CASE
                                        WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
                                        THEN 1
                                        ELSE 0
                                    END+CASE
                                            WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
                                            THEN 1
                                            ELSE 0
                                        END;
    SET @TotalTimeDiff =
    (
        SELECT DATEDIFF(SECOND,
                       (
                           SELECT CONVERT(TIME, @DateFrom)
                       ),
                       (
                           SELECT CONVERT(TIME, @DateTo)
                       )) / 3600.0
    );
    RETURN  (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
    END
    GO
    

    本文介绍了完整的方法: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

  • 5月前
    4 #

    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @WORKINGHOURS INT
    DECLARE @Days INT
    SET @StartDate = '2010/01/01'
    SET @EndDate = '2010/04/01'
    --number of working days
    SELECT @Days = 
       (DATEDIFF(dd, @StartDate, @EndDate) + 1)
      -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
      -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
    --8 hours a day    
    SET @WORKINGHOURS = @Days * 8 
    SELECT @WORKINGHOURS
    

  • 5月前
    5 #

    @Pavanred的替代解決方案,从更多基於資料的角度出發:

    建立一个包含所有要考虑的日期的表.每天設置一些工作時間,例如:

    WorkingDate Hours Comment
    =========== ===== ==================
     1 Jan 2011     0 Saturday
     2 Jan 2011     0 Sunday
     3 Jan 2011     0 Public Holiday
     4 Jan 2011     8 Normal working day
     5 Jan 2011     8 Normal working day
     -- and so on, for all the days you want to report on.
    

    這將需要少量設置-您可以自動將其預填充數週而不是週末,然後根据需要調整以適應公共假期等。

    但是,您在設置中失去的是,可以轻松查詢:

    SELECT
      SUM(Hours) 
    FROM
      working_days 
    WHERE
      WorkingDate BETWEEN @StartDate AND @EndDate
    

    ...如果您需要開始為定義工作日的規則添加更複雜的規則,或者您的工作時間根据一天等而變化,這可以作為一種更簡單的方法。

    這也使規則更易於"編輯",因為您無需更改任何實際代碼即可更改工作日的定義,添加公共假期等。

  • java:自動調整JTable列的宽度
  • windows phone 7:使用Silverlight在wP7應用程式中顯示GIF