TAGS :Viewed: 9 - Published at: a few seconds ago

[ Calculating number of seconds between two dates and categorize ]

I have a problem I need to solve using SQL. The problem is that I have to calculate how many seconds between the date are in working hours, how many are outside working hours and how many are in weekends. Working hours are between 0800 and 1600. Some sort of function should be able to do this, but I can’t wrap my head around how. Can anyone help me?

Example data(SQL Server 2008 R2 SP2):

Opendate: 2014-10-01 10:33:57.767 Closedate: 2014-10-09 10:33:52.733

Answer 1


I'm using these functions that return the non-weekend seconds between two dates:

CREATE FUNCTION [dbo].[DateDiff_NoWeekends](
    @date1 DATETIME,
    @date2 DATETIME
)

RETURNS INT AS BEGIN
    DECLARE @retValue INT

    SET @date1 = dbo.__CorrectDate(@date1, 1)
    SET @date2 = dbo.__CorrectDate(@date2, 0)

    IF (@date1 >= @date2)
        SET @retValue = 0
    ELSE BEGIN
        DECLARE @days INT, @weekday INT
        SET @days = DATEDIFF(d, @date1, @date2)
        SET @weekday = DATEPART(dw, @date1) - 1

        SET @retValue = DATEDIFF(s, @date1, @date2) - 2 * 24 * 3600 * ((@days + @weekday) / 7) 
    END

    RETURN @retValue
END

GO


CREATE FUNCTION [dbo].[__CorrectDate](
    @date DATETIME,
    @forward INT
)

RETURNS DATETIME AS BEGIN
    IF (DATEPART(dw, @date) > 5) BEGIN

        IF (@forward = 1) BEGIN
            SET @date = @date + (8 - DATEPART(dw, @date))
            SET @date = DateAdd(Hour, (8 - DatePart(Hour, @date)), @date)
        END ELSE BEGIN
            SET @date = @date - (DATEPART(dw, @date)- 5)
            SET @date = DateAdd(Hour, (18 - DatePart(Hour, @date)), @date)
        END
        SET @date = DateAdd(Minute, -DatePart(Minute, @date), @date)
        SET @date = DateAdd(Second, -DatePart(Second, @date), @date)
    END

    RETURN @date
END

This should help to get started to find also the solution for the other tasks.

Here's a sql-fiddle demo with your sample data:

SELECT [weekday-seconds between] =
    (dbo.DateDiff_NoWeekends('2014-10-01 10:33:57.767','2014-10-09 10:33:52.733'))

=> 518395