Function to get Business Hours

Hello,

Its been a while since my last blog post. Today I am blogging about a function that I had to work on to calculate business hours. Per my requirement the start time is 8:30AM and end time is 17:30PM and no business holidays but weekends are off.

Here is the function:


Create FUNCTION [dbo].[GetBusinessHours]
(
@BegDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @NumOfHours INT;
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), DateTimes (dt) AS (
SELECT TOP (DATEDIFF(hh, @BegDate, @EndDate))
DATEADD(hh, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @BegDate)
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
SELECT
@NumOfHours = COUNT(1)
FROM
DateTimes dt
WHERE 1 = 1
AND DATEPART(dw, dt.dt) NOT IN (1,7)
AND CAST(dt.dt AS TIME) BETWEEN '08:30:00' AND '17:30:00'
RETURN @NumOfHours
END

GO

Hope it helps.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s