[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 374

# SQL Server: Elapsed number of seconds during weekday business hours.

LOOKING FOR SQL SERVER (2000) SCALAR FUNCTION OR SIMILAR SOLUTION....
Need to calculate the number of elapsed seconds between two DateTimes variables.
Exceptions are as follows:
1. DO NOT count weekends (12:01AM Saturday thru 11:59PM Sunday)
2. Do not count hours outside of weekday business hours. (7:00PM thru 7:00AM)

In short, we need to only count the elapsed number seconds that are from MON-FRO 7:00AM to 7:00PM
Louis
0
Louis Capece
• 5
• 2
2 Solutions

Commented:
IF EXISTS (SELECT *
FROM   sysobjects
WHERE  name = N'ssdiff')
DROP FUNCTION ssdiff
GO

CREATE FUNCTION ssdiff (@dt1 datetime, @dt2 datetime)
RETURNS int
AS
BEGIN
declare @mdt1 datetime, @mdt2 datetime, @days1 int, @days2 int, @wks int,
@totdays int, @secs int, @ptdy1secs int, @ptdy2secs int

select      @days1 = case (datepart(dw,@dt1)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt1) end,
@days2 = case (datepart(dw,@dt2)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt2) end

select @wks = datediff(wk,@dt1, @dt2)

if @wks > 1
set @totdays = (@wks * 5) + @days1 + @days2
else
set @totdays = @days1 + @days2

if (datepart(dw,@dt2)) between 2 and 6
begin
select @mdt1 = convert(datetime, convert(varchar(25), @dt1, 101))
set @ptdy1secs = case
when datediff(s,@mdt1, @dt1) < 25200 then 0
when  datediff(s,@mdt1, @dt1) > 25200 and datediff(s,@mdt1, @dt1) <= 68400 then datediff(s,@mdt1, @dt1) - 25200
else 68400
end
end
else
set @ptdy1secs = 0

if (datepart(dw,@dt2)) between 2 and 6
begin
select @mdt2 = convert(datetime, convert(varchar(25), @dt2, 101))
set @ptdy2secs = case
when datediff(s,@mdt2, @dt2) >= 68400 then 0
when  datediff(s,@mdt2, @dt2) < 68400 and datediff(s,@mdt2, @dt2) > 25200 then 68400- datediff(s,@mdt2, @dt2)
else 68400
end
end
else
set @ptdy2secs = 0

set @secs = (12*3600*@totdays) + @ptdy1secs + @ptdy2secs

--select @dt1, @dt2, datepart(dw,@dt1) wkdy1, datepart(dw,@dt2) wkdy2, @days1, @days2, @wks wks,
--      @totdays totdays, @secs secs, @ptdy1secs pt1, @ptdy2secs pt2

return @secs
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.ssdiff (getdate()-78, getdate())
GO

0

Commented:
Bug in that above

-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM   sysobjects
WHERE  name = N'ssdiff')
DROP FUNCTION ssdiff
GO

CREATE FUNCTION ssdiff (@dt1 datetime, @dt2 datetime)
RETURNS int
AS
BEGIN
declare @mdt1 datetime, @mdt2 datetime, @days1 int, @days2 int, @wks int,
@totdays int, @secs int, @ptdy1secs int, @ptdy2secs int

select      @days1 = case (datepart(dw,@dt1)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt1) end,
@days2 = case (datepart(dw,@dt2)) when 1 then 5 when 7 then 0 else 6-datepart(dw,@dt2) end

select @wks = datediff(wk,@dt1, @dt2)

if @wks > 1
set @totdays = (@wks * 5) + @days1 + @days2
else
set @totdays = @days1 + @days2

if (datepart(dw,@dt2)) between 2 and 6
begin
select @mdt1 = convert(datetime, convert(varchar(25), @dt1, 101))
set @ptdy1secs = case
when datediff(s,@mdt1, @dt1) < 25200 then 0
when  datediff(s,@mdt1, @dt1) > 25200 and datediff(s,@mdt1, @dt1) <= 68400 then datediff(s,@mdt1, @dt1) - 25200
else 43200
end
end
else
set @ptdy1secs = 0

if (datepart(dw,@dt2)) between 2 and 6
begin
select @mdt2 = convert(datetime, convert(varchar(25), @dt2, 101))
set @ptdy2secs = case
when datediff(s,@mdt2, @dt2) >= 68400 then 0
when  datediff(s,@mdt2, @dt2) < 68400 and datediff(s,@mdt2, @dt2) > 25200 then 68400- datediff(s,@mdt2, @dt2)
else 43200
end
end
else
set @ptdy2secs = 0

set @secs = (12*3600*@totdays) + @ptdy1secs + @ptdy2secs

--select @dt1, @dt2, datepart(dw,@dt1) wkdy1, datepart(dw,@dt2) wkdy2, @days1, @days2, @wks wks,
--      @totdays totdays, @secs secs, @ptdy1secs pt1, @ptdy2secs pt2

return @secs
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.ssdiff (getdate()-78, getdate())
GO

0

Senior DBACommented:
First, create a table of sequential numbers from **0** to at least the max number of days difference you will need -- or just create 2000 or 3000 rows to be safe :-).  For example:
CREATE TABLE seqNums (
seqNum SMALLINT NOT NULL,
CONSTRAINT seqNums_CI
UNIQUE CLUSTERED (seqNum)
)
-- load table with values from *0* to, say, 2000

Then:

CREATE FUNCTION SecondsDiff (
@date1 DATETIME,
@date2 DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @totalSeconds INT
SET @totalSeconds = 0
SELECT @totalSeconds = @totalSeconds +
CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, seqNum, @date1)) IN ('Saturday', 'Sunday')
THEN 0
ELSE CASE
WHEN seqNum = 0 --first (maybe only) day
THEN CASE WHEN DATEPART(HOUR, @date1) >= 19 THEN 0
ELSE DATEDIFF(SECOND,
CASE WHEN DATEPART(HOUR, @date1) < 07
THEN CONVERT(CHAR(8), @date1, 112) + ' 07:00' ELSE @date1 END,
CASE WHEN (DATEDIFF(DAY, @date1, @date2) > 0 OR DATEPART(HOUR, @date2) >= 19)
THEN CONVERT(CHAR(8), @date1, 112) + ' 19:00' ELSE @date2 END)
END
WHEN seqNum = DATEDIFF(DAY, @date1, @date2) --last day
THEN CASE WHEN DATEPART(HOUR, @date2) < 7 THEN 0
WHEN DATEPART(HOUR, @date2) >= 19 THEN 12 * 60 * 60 -- full day
ELSE DATEDIFF(SECOND, CONVERT(CHAR(8), @date2, 112) + ' 07:00', @date2)
END
ELSE 12 * 60 * 60 -- in-between day, count all work hours
END
END
FROM seqNums
WHERE seqNum BETWEEN 0 AND DATEDIFF(DAY, @date1, @date2)
RETURN @totalSeconds
END --FUNCTION
0

Senior DBACommented:
The SELECT computes the seconds for each day separately, based on adding the seqNum to the starting date (@date1).

I think the ssdiff function still has at least one bug:

DECLARE @date1 DATETIME
DECLARE @date2 DATETIME
SET @date1 = '20070722 08:00'
SET @date2 = '20070727 18:45'

SELECT dbo.SecondsDiff(@date1, @date2),
dbo.SecondsDiff(@date1, @date2) / 3600.00
SELECT dbo.ssdiff(@date1, @date2),
dbo.ssdiff(@date1, @date2) / 3600.00

You can tell fairly quickly just by looking at the dates that the correct total should be a full week minus 15 minutes.
0

Commented:
Yes, quite

0

Commented:
First off, create the function below, which returns the 00:00:00.000 time date of any given date

IF EXISTS (SELECT *
FROM   sysobjects
WHERE  name = N'tzero')
DROP FUNCTION tzero
GO

CREATE FUNCTION tzero (@dt datetime)
RETURNS datetime
AS
BEGIN
return convert(datetime, convert(varchar(25), @dt, 101),101)
END
go

select dbo.tzero(getdate())
go
0

Commented:
Now, use this query batch to test the logic, by changing set statements for @dt1 and @dt2. Once your satisifed it's correct, it can be wrapped up in a function

declare
@sign smallint,
@dt1 datetime,
@dt2 datetime,
@tmp datetime,
@0700s int,
@1900s int,
@dt1times int,
@dt2times int,
@secs int

set @0700s = 7*3600
set @1900s = 19*3600

set @dt1 = convert (datetime, '2007-07-23 07:00:00.000', 121)
set @dt2 = convert (datetime, '2007-07-23 19:00:00.000', 121)
set @sign = 1

-- are the dates wrong way round
if @dt1>@dt2
begin
set @sign = -1
set @tmp = @dt1
set @dt1 = @dt2
set @dt2 = @tmp
end

-- if either date is a sat or sun, set to monday 00:00
select      @dt1 = case (datepart(dw,@dt1))
when 1 then dbo.tzero(@dt1)+1
when 7 then dbo.tzero(@dt1)+2
else @dt1
end,
@dt2 = case (datepart(dw,@dt2))
when 1 then dbo.tzero(@dt2)+1
when 7 then dbo.tzero(@dt2)+2
else @dt2
end

set @dt1times = datediff(s,dbo.tzero(@dt1), @dt1)
select @dt1 =
case
when @dt1times < @0700s then dateadd(s,@0700s,dbo.tzero(@dt1))
when @dt1times > @1900s then dateadd(s,@1900s,dbo.tzero(@dt1))
else @dt1
end

set @dt2times = datediff(s,dbo.tzero(@dt2), @dt2)
select @dt2 =
case
when @dt2times < @0700s then dateadd(s,@0700s,dbo.tzero(@dt2))
when @dt2times > @1900s then dateadd(s,@1900s,dbo.tzero(@dt2))
else @dt2
end

-- over second difference
set @secs = datediff(s, @dt1,@dt2)

-- take out the non work time from each day
-- the 19:00 to 07:00
set @secs = @secs - (datediff(d,@dt1,@dt2)*12*3600)

-- take out the non work time for each weeekend
-- the 07:00 to 19:00
set @secs = @secs - (datediff(wk,@dt1,@dt2)*12*3600*2)

-- sign change
set @secs = @secs * @sign

select      @secs secs
0

## Featured Post

• 5
• 2
Tackle projects and never again get stuck behind a technical roadblock.