# Calculating number of seconds between two dates and categorize - Database

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

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
``````