Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home SQL Server SQL Server Get number of working days and business hours between start date and end date

SQL Server Get number of working days and business hours between start date and end date

This tutorial demonstrates 2 ways for you how to obtain the number of working days and business hours in SQL Server based on between date start and end date parameters.This function is more popular in doing business related to human resource, particularly in calculating salary of employee :).

Below is a case for demonstration :
The input :
+ Start date : ‘2012-03-01’
+ End date : ‘2012-03-31’
The output :

Working Days and Business Hours Result

Working Days and Business Hours Result

1. The first way : Get total number of days based on DATEDIFF function then subtract weekend days

--Calculate and return the number of workdays based on the input parameters.   
CREATE FUNCTION [dbo].[fGetWorkingDays]
(
    @StartDate DATETIME, -- start date
    @EndDate   DATETIME  -- end date
)
RETURNS INT
AS
BEGIN  
    --If the Start Date or End Date is null, return NULL.
    IF (@StartDate IS NULL OR @EndDate IS NULL)
        RETURN NULL
 
    --If @StartDate is greater than @EndDate, return -1
     IF @StartDate > @EndDate
        RETURN -1
 
     RETURN 
     (
        SELECT        
          (DATEDIFF(dd,@StartDate, @EndDate) + 1) -- Get total number of days including weekends               
        - (DATEDIFF(wk,@StartDate, @EndDate) * 2) -- Subtact 2 days for each full weekend        
        - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' -- If Start Date is Sunday, subtract 1
	      THEN 1
	      ELSE 0
          END)       		    
        - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' -- If End Date is Saturday, subtract 1
	      THEN 1
	      ELSE 0
           END)
      )
END

--Calculate and return the number of workdays based on the input parameters. CREATE FUNCTION [dbo].[fGetWorkingDays] ( @StartDate DATETIME, -- start date @EndDate DATETIME -- end date ) RETURNS INT AS BEGIN --If the Start Date or End Date is null, return NULL. IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN NULL --If @StartDate is greater than @EndDate, return -1 IF @StartDate > @EndDate RETURN -1 RETURN ( SELECT (DATEDIFF(dd,@StartDate, @EndDate) + 1) -- Get total number of days including weekends - (DATEDIFF(wk,@StartDate, @EndDate) * 2) -- Subtact 2 days for each full weekend - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' -- If Start Date is Sunday, subtract 1 THEN 1 ELSE 0 END) - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' -- If End Date is Saturday, subtract 1 THEN 1 ELSE 0 END) ) END

2. The second way : Get total number of days based on UDF function fGetListOfDates – Get list of dates between start date and end date without including weekend days

--Calculate and return the number of workdays based on the input parameters.   
CREATE FUNCTION [dbo].[fGetWorkingDays]
(
    @StartDate DATETIME, -- start date
    @EndDate   DATETIME  -- end date
)
RETURNS INT
AS
BEGIN  
    --If the Start Date or End Date is null, return NULL.
    IF (@StartDate IS NULL OR @EndDate IS NULL)
        RETURN NULL
 
    --If @StartDate is greater than @EndDate, return -1
     IF @StartDate > @EndDate
        RETURN -1
 
     RETURN 
     (
	SELECT COUNT(LD.NameOfDay) 
	  FROM dbo.fGetListOfDates(@StartDate, @EndDate) LD
         WHERE LD.NameOfDay NOT IN ('Sunday','Saturday')
     )
END

--Calculate and return the number of workdays based on the input parameters. CREATE FUNCTION [dbo].[fGetWorkingDays] ( @StartDate DATETIME, -- start date @EndDate DATETIME -- end date ) RETURNS INT AS BEGIN --If the Start Date or End Date is null, return NULL. IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN NULL --If @StartDate is greater than @EndDate, return -1 IF @StartDate > @EndDate RETURN -1 RETURN ( SELECT COUNT(LD.NameOfDay) FROM dbo.fGetListOfDates(@StartDate, @EndDate) LD WHERE LD.NameOfDay NOT IN ('Sunday','Saturday') ) END

3. After create this function,then run this below statements, we will get expected result as the above image

DECLARE @StartDate DATETIME,
        @EndDate DATETIME
SET @StartDate = dbo.GetFirstDayOfMonth(GETDATE(), 0) -- @StartDate = '03-01-2012'
SET @EndDate = dbo.GetLastDayOfMonth(GETDATE(), 0)    -- @EndDate = '03-31-2012'     
SELECT dbo.fGetWorkingDays(@StartDate,@EndDate) AS NumberOfWorkingDays
SELECT dbo.fGetWorkingDays(@StartDate,@EndDate) * 8 AS BusinessHours

DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = dbo.GetFirstDayOfMonth(GETDATE(), 0) -- @StartDate = '03-01-2012' SET @EndDate = dbo.GetLastDayOfMonth(GETDATE(), 0) -- @EndDate = '03-31-2012' SELECT dbo.fGetWorkingDays(@StartDate,@EndDate) AS NumberOfWorkingDays SELECT dbo.fGetWorkingDays(@StartDate,@EndDate) * 8 AS BusinessHours

With above test script , it mentions to two functions GetFirstDayOfMonth and GetLastDayOfMonth, in order to understand these functions you can refer to SQL Server get first/last day of previous/current/next month

Hope it helpful for you :)!

Mar 13, 2012quynhnguyen
Check Version, Processor And Memory In MacBookValidate Date With JQuery And Date Object
You Might Also Like:
  • SQL Server Get list of dates between start date and end date
  • SQL Server get first/last day of previous/current/next month
  • C# ASP.Net Validate Date
  • Validate Date With JQuery And Date Object
  • If blogging is a business then how to start
  • PHP Connect To MS SQL Server
  • Replace String With NText Or Text Data Type In MS SQL Server
  • Capture And Display Execution Time Of SQL Query In SQL Server
  • Xcode get current date, month, year
  • SQL SERVER How to import Text/CSV file into table
quynhnguyen
10 years ago SQL ServerDATEDIFF, DATENAME, Function, SQL Server, UDF334
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,554 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,892 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,745 views
JQuery Allow only numeric characters or only alphabet characters in textbox
15,069 views
C# Read Json From URL And Parse/Deserialize Json
11,802 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development