Count work days between two dates

Question

How can I calculate the number of work days between two dates in SQL Server    Monday to Friday and it must be T-SQL

User · Accepted Answer

For workdays  Monday to Friday  you can do it with a single SELECT  like this   DECLARE  StartDate DATETIME DECLARE  EndDate DATETIME SET  StartDate    2008 10 01  SET  EndDate    2008 10 31    SELECT     DATEDIFF dd   StartDate   EndDate    1    - DATEDIFF wk   StartDate   EndDate    2    - CASE WHEN DATENAME dw   StartDate     Sunday  THEN 1 ELSE 0 END    - CASE WHEN DATENAME dw   EndDate     Saturday  THEN 1 ELSE 0 END    If you want to include holidays  you have to work it out a bit

User · Answer

Here is a version that works well  I think    Holiday table contains Holiday date columns that contains holidays your company observe   DECLARE  RAWDAYS INT     SELECT  RAWDAYS    DATEDIFF day   StartDate   EndDate  -- 1                     -  2   DATEDIFF  week   StartDate   EndDate                           CASE WHEN DATENAME dw   StartDate     Saturday  THEN 1 ELSE 0 END                     - CASE WHEN DATENAME dw   EndDate     Saturday  THEN 1 ELSE 0 END      SELECT   RAWDAYS - COUNT          FROM HOLIDAY NumberOfBusinessDays     WHERE  Holiday Date  BETWEEN  StartDate 1 AND  EndDate

User · Answer

I found the below TSQL a fairly elegant solution  I don t have permissions to run functions   I found the DATEDIFF ignores DATEFIRST and I wanted my first day of the week to be a Monday  I also wanted the first working day to be set a zero and if it falls on a weekend Monday will be a zero  This may help someone who has a slightly different requirement      It does not handle bank holidays  SET DATEFIRST 1 SELECT   DATEDIFF DD    StartDate    EndDate            - DATEDIFF wk    StartDate    EndDate            - DATEDIFF wk  DATEADD dd -  DATEFIRST  StartDate    DATEADD dd -  DATEFIRST  EndDate     AS  WorkingDays   FROM   Your Table

User · Answer

As with DATEDIFF  I do not consider the end date to be part of the interval  The number of  for example  Sundays between  StartDate and  EndDate is the number of Sundays between an  initial  Monday and the  EndDate minus the number of Sundays between this  initial  Monday and the  StartDate  Knowing this  we can calculate the number of workdays as follows   DECLARE  StartDate DATETIME DECLARE  EndDate DATETIME SET  StartDate    2018 01 01  SET  EndDate    2019 01 01   SELECT DATEDIFF Day   StartDate   EndDate  -- Total Days   -  DATEDIFF Day  0   EndDate  7 - DATEDIFF Day  0   StartDate  7  -- Sundays   -  DATEDIFF Day  -1   EndDate  7 - DATEDIFF Day  -1   StartDate  7  -- Saturdays   Best regards

User · Answer

For difference between dates including holidays I went this way   1  Table with Holidays       CREATE TABLE  dbo   Holiday    Id   int  IDENTITY 1 1  NOT NULL   Name   nvarchar  50  NULL   Date   datetime  NOT NULL    2  I had my plannings Table like this and wanted to fill column Work Days which was empty       CREATE TABLE  dbo   Plan Phase    Id   int  IDENTITY 1 1  NOT NULL   Id Plan   int  NOT NULL   Id Phase   int  NOT NULL   Start Date   datetime  NULL   End Date   datetime  NULL   Work Days   int  NULL    3  So in order to get  Work Days  to later fill in my column just had to   SELECT Start Date  End Date    DATEDIFF dd  Start Date  End Date    1  - DATEDIFF wk  Start Date  End Date    2  - SELECT COUNT    From Holiday Where Date   gt   Start Date AND Date  lt   End Date  - CASE WHEN DATENAME dw  Start Date     Sunday  THEN 1 ELSE 0 END  - CASE WHEN DATENAME dw  End Date     Saturday  THEN 1 ELSE 0 END  - CASE WHEN  SELECT COUNT    From Holiday Where Start Date    Date   gt  0 THEN 1 ELSE 0 END  - CASE WHEN  SELECT COUNT    From Holiday Where End Date    Date   gt  0 THEN 1 ELSE 0 END  AS Work Days from Plan Phase   Hope that I could help   Cheers

User · Answer

My version of the accepted answer as a function using DATEPART  so I don t have to do a string comparison on the line with   DATENAME dw   StartDate     Sunday    Anyway  here s my business datediff function  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  CREATE FUNCTION BDATEDIFF        startdate as DATETIME       enddate as DATETIME   RETURNS INT AS BEGIN     DECLARE  res int  SET  res    DATEDIFF dd   startdate   enddate    1      - DATEDIFF wk   startdate   enddate    2      - CASE WHEN DATEPART dw   startdate    1 THEN 1 ELSE 0 END      - CASE WHEN DATEPART dw   enddate    7 THEN 1 ELSE 0 END       RETURN  res END GO

User · Answer

CREATE FUNCTION x        StartDate DATETIME       EndDate DATETIME   RETURNS INT AS BEGIN     DECLARE  Teller INT      SET  StartDate   DATEADD dd 1  StartDate       SET  Teller   0     IF DATEDIFF dd  StartDate  EndDate   lt   0     BEGIN         SET  Teller   0      END     ELSE     BEGIN         WHILE             DATEDIFF dd  StartDate  EndDate   gt   0         BEGIN             IF DATEPART dw  StartDate   lt  6             BEGIN                 SET  Teller    Teller   1             END             SET  StartDate   DATEADD dd 1  StartDate          END     END     RETURN  Teller END

User · Answer

If you need to add work days to a given date  you can create a function that depends on a calendar table  described below   CREATE TABLE Calendar     dt SMALLDATETIME PRIMARY KEY     IsWorkDay BIT     --fill the rows with normal days  weekends and holidays    create function AddWorkingDays   initialDate smalldatetime   numberOfDays int      returns smalldatetime as       begin         declare  result smalldatetime         set  result                          select t dt from                               select dt  ROW NUMBER   over  order by dt  as daysAhead from calendar                  where dt  gt   initialDate                 and IsWorkDay   1                   t             where t daysAhead    numberOfDays                    return  result     end

User · Answer

One approach is to  walk the dates  from start to finish in conjunction with a case expression which checks if the day is not a Saturday or a Sunday and flagging it 1 for weekday  0 for weekend   And in the end just sum flags it would be equal to the count of 1-flags as the other flag is 0  to give you the number of weekdays   You can use a GetNums startNumber endNumber  type of utility function which generates a series of numbers for  looping  from start date to end date  Refer http   tsql solidq com SourceCodes GetNums txt for an implementation  The logic can also be extended to cater for holidays say if you have a holidays table   declare  date1 as datetime    19900101  declare  date2 as datetime    19900120   select  sum case when DATENAME DW currentDate  not in   Saturday    Sunday   then 1 else 0 end  as noOfWorkDays from dbo GetNums 0 DATEDIFF day  date1   date2 -1  as Num cross apply  select DATEADD day n  date1   as Dates currentDate

User · Answer

If you need to add work days to a given date  you can create a function that depends on a calendar table  described below   CREATE TABLE Calendar     dt SMALLDATETIME PRIMARY KEY     IsWorkDay BIT     --fill the rows with normal days  weekends and holidays    create function AddWorkingDays   initialDate smalldatetime   numberOfDays int      returns smalldatetime as       begin         declare  result smalldatetime         set  result                          select t dt from                               select dt  ROW NUMBER   over  order by dt  as daysAhead from calendar                  where dt  gt   initialDate                 and IsWorkDay   1                   t             where t daysAhead    numberOfDays                    return  result     end

User · Answer

This is basically CMS s answer without the reliance on a particular language setting  And since we re shooting for generic  that means it should work for all   datefirst settings as well   datediff day   lt start gt    lt end gt     1 - datediff week   lt start gt    lt end gt     2        if start is a Sunday  adjust by -1        case when datepart weekday   lt start gt     8 -   datefirst then -1 else 0 end        if end is a Saturday  adjust by -1        case when datepart weekday   lt end gt      13 -   datefirst    7   1 then -1 else 0 end   datediff week       always uses a Saturday-to-Sunday boundary for weeks  so that expression is deterministic and doesn t need to be modified  as long as our definition of weekdays is consistently Monday through Friday   Day numbering does vary according to the   datefirst setting and the modified calculations handle this correction with the small complication of some modular arithmetic   A cleaner way to deal with the Saturday Sunday thing is to translate the dates prior to extracting a day of week value  After shifting  the values will be back in line with a fixed  and probably more familiar  numbering that starts with 1 on Sunday and ends with 7 on Saturday   datediff day   lt start gt    lt end gt     1 - datediff week   lt start gt    lt end gt     2     case when datepart weekday  dateadd day    datefirst   lt start gt      1 then -1 else 0 end     case when datepart weekday  dateadd day    datefirst   lt end gt        7 then -1 else 0 end   I ve tracked this form of the solution back at least as far as 2002 and an Itzik Ben-Gan article   https   technet microsoft com en-us library aa175781 v sql 80  aspx  Though it needed a small tweak since newer date types don t allow date arithmetic  it is otherwise identical   EDIT  I added back the  1 that had somehow been left off  It s also worth noting that this method always counts the start and end days  It also assumes that the end date is on or after the start date

User · Answer

I m a few points shy of commenting privileges   If you decide to forgo the  1 day in CMS s elegant solution  note that if your start date and end date are in the same weekend  you get a negative answer  Ie   2008 10 26 to 2008 10 26 returns -1   my rather simplistic solution   select  Result      CMS s answer    if    Result  lt  0          select  Result   0     RETURN  Result      which also sets all erroneous posts with start date after end date to zero  Something you may or may not be looking for

User · Answer

One approach is to  walk the dates  from start to finish in conjunction with a case expression which checks if the day is not a Saturday or a Sunday and flagging it 1 for weekday  0 for weekend   And in the end just sum flags it would be equal to the count of 1-flags as the other flag is 0  to give you the number of weekdays   You can use a GetNums startNumber endNumber  type of utility function which generates a series of numbers for  looping  from start date to end date  Refer http   tsql solidq com SourceCodes GetNums txt for an implementation  The logic can also be extended to cater for holidays say if you have a holidays table   declare  date1 as datetime    19900101  declare  date2 as datetime    19900120   select  sum case when DATENAME DW currentDate  not in   Saturday    Sunday   then 1 else 0 end  as noOfWorkDays from dbo GetNums 0 DATEDIFF day  date1   date2 -1  as Num cross apply  select DATEADD day n  date1   as Dates currentDate

User · Answer

DECLARE  TotalDays INT  WorkDays INT  DECLARE  ReducedDayswithEndDate INT  DECLARE  WeekPart INT  DECLARE  DatePart INT   SET  TotalDays  DATEDIFF day   StartDate   EndDate   1  SELECT  ReducedDayswithEndDate   CASE DATENAME weekday   EndDate    WHEN  Saturday  THEN 1   WHEN  Sunday  THEN 2   ELSE 0 END   SET  TotalDays  TotalDays- ReducedDayswithEndDate  SET  WeekPart  TotalDays 7   SET  DatePart  TotalDays 7   SET  WorkDays   WeekPart 5   DatePart   RETURN  WorkDays

User · Answer

In Calculating Work Days you can find a good article about this subject  but as you can see it is not that advanced   --Changing current database to the Master database allows function to be shared by everyone  USE MASTER GO --If the function already exists  drop it  IF EXISTS       SELECT       FROM dbo SYSOBJECTS     WHERE ID   OBJECT ID N  dbo   fn WorkDays        AND XType IN  N FN   N IF   N TF     DROP FUNCTION  dbo   fn WorkDays  GO  CREATE FUNCTION dbo fn WorkDays --Presets --Define the input parameters  OK if reversed by mistake          StartDate DATETIME       EndDate   DATETIME   NULL -- EndDate replaced by  StartDate when DEFAULTed    --Define the output data type  RETURNS INT  AS --Calculate the RETURN of the function  BEGIN     --Declare local variables     --Temporarily holds  EndDate during date reversal      DECLARE  Swap DATETIME      --If the Start Date is null  return a NULL and exit      IF  StartDate IS NULL         RETURN NULL      --If the End Date is null  populate with Start Date value so will have two dates  required by DATEDIFF below        IF  EndDate IS NULL         SELECT  EndDate    StartDate      --Strip the time element from both dates  just to be safe  by converting to whole days and back to a date      --Usually faster than CONVERT      --0 is a date  01 01 1900 00 00 00 000       SELECT  StartDate   DATEADD dd DATEDIFF dd 0  StartDate   0                EndDate     DATEADD dd DATEDIFF dd 0  EndDate     0       --If the inputs are in the wrong order  reverse them       IF  StartDate  gt   EndDate         SELECT  Swap         EndDate                  EndDate      StartDate                  StartDate    Swap      --Calculate and return the number of workdays using the input parameters      --This is the meat of the function      --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes       RETURN           SELECT         --Start with total number of days including weekends          DATEDIFF dd  StartDate   EndDate  1          --Subtact 2 days for each full weekend         - DATEDIFF wk  StartDate   EndDate  2          --If StartDate is a Sunday  Subtract 1         - CASE WHEN DATENAME dw   StartDate     Sunday              THEN 1             ELSE 0         END          --If EndDate is a Saturday  Subtract 1         - CASE WHEN DATENAME dw   EndDate     Saturday              THEN 1             ELSE 0         END                END GO   If you need to use a custom calendar  you might need to add some checks and some parameters  Hopefully it will provide a good starting point

User · Answer

Using a date table       DECLARE           StartDate date    2014-01-01            EndDate date    2014-01-31        SELECT          COUNT    As NumberOfWeekDays     FROM dbo Calendar     WHERE CalendarDate BETWEEN  StartDate AND  EndDate       AND IsWorkDay   1    If you don t have that  you can use a numbers table       DECLARE       StartDate datetime    2014-01-01        EndDate datetime    2014-01-31        SELECT      SUM CASE WHEN DATEPART dw  DATEADD dd  Number-1   StartDate   BETWEEN 2 AND 6 THEN 1 ELSE 0 END  As NumberOfWeekDays     FROM dbo Numbers     WHERE Number  lt   DATEDIFF dd   StartDate   EndDate    1 -- Number table starts at 1  we want a 0 base   They should both be fast and it takes out the ambiguity complexity  The first option is the best but if you don t have a calendar table you can allways create a numbers table with a CTE

User · Answer

As with DATEDIFF  I do not consider the end date to be part of the interval  The number of  for example  Sundays between  StartDate and  EndDate is the number of Sundays between an  initial  Monday and the  EndDate minus the number of Sundays between this  initial  Monday and the  StartDate  Knowing this  we can calculate the number of workdays as follows   DECLARE  StartDate DATETIME DECLARE  EndDate DATETIME SET  StartDate    2018 01 01  SET  EndDate    2019 01 01   SELECT DATEDIFF Day   StartDate   EndDate  -- Total Days   -  DATEDIFF Day  0   EndDate  7 - DATEDIFF Day  0   StartDate  7  -- Sundays   -  DATEDIFF Day  -1   EndDate  7 - DATEDIFF Day  -1   StartDate  7  -- Saturdays   Best regards

User · Answer

I took the various examples here  but in my particular situation we have a  PromisedDate for delivery and a  ReceivedDate for the actual receipt of the item   When an item was received before the  PromisedDate  the calculations were not totaling correctly unless I ordered the dates passed into the function by calendar order   Not wanting to check the dates every time  I changed the function to handle this for me   Create FUNCTION  dbo   fnGetBusinessDays      PromiseDate date    ReceivedDate date   RETURNS integer AS BEGIN  DECLARE  days integer   SELECT  days        Case when  PromiseDate  gt   ReceivedDate Then         DATEDIFF d  PromiseDate  ReceivedDate             ABS DATEDIFF wk  PromiseDate  ReceivedDate     2           CASE              WHEN DATENAME dw   PromiseDate   lt  gt   Saturday  AND DATENAME dw   ReceivedDate     Saturday  THEN 1              WHEN DATENAME dw   PromiseDate     Saturday  AND DATENAME dw   ReceivedDate   lt  gt   Saturday  THEN -1              ELSE 0         END            Select COUNT    FROM CompanyHolidays              WHERE HolidayDate BETWEEN  ReceivedDate AND  PromiseDate              AND DATENAME dw  HolidayDate   lt  gt   Saturday  AND DATENAME dw  HolidayDate   lt  gt   Sunday       Else         DATEDIFF d  PromiseDate  ReceivedDate   -         ABS DATEDIFF wk  PromiseDate  ReceivedDate     2  -             CASE                  WHEN DATENAME dw   PromiseDate   lt  gt   Saturday  AND DATENAME dw   ReceivedDate     Saturday  THEN 1                  WHEN DATENAME dw   PromiseDate     Saturday  AND DATENAME dw   ReceivedDate   lt  gt   Saturday  THEN -1                  ELSE 0             END -          Select COUNT    FROM CompanyHolidays              WHERE HolidayDate BETWEEN  PromiseDate and  ReceivedDate              AND DATENAME dw  HolidayDate   lt  gt   Saturday  AND DATENAME dw  HolidayDate   lt  gt   Sunday       End    RETURN   days   END

User · Answer

I borrowed some ideas from others to create my solution   I use inline code to ignore weekends and U S  federal holidays   In my environment  EndDate may be null  but it will never precede StartDate   CREATE FUNCTION dbo ufn CalculateBusinessDays   StartDate DATE   EndDate DATE   NULL   RETURNS INT AS  BEGIN DECLARE  TotalBusinessDays INT   0  DECLARE  TestDate DATE    StartDate    IF  EndDate IS NULL     RETURN NULL   WHILE  TestDate  lt   EndDate BEGIN     DECLARE  Month INT   DATEPART MM   TestDate       DECLARE  Day INT   DATEPART DD   TestDate       DECLARE  DayOfWeek INT   DATEPART WEEKDAY   TestDate  - 1  --Monday   1  Tuesday   2  etc      DECLARE  DayOccurrence INT     Day - 1    7   1  --Nth day of month  3rd Monday  for example       --Increment business day counter if not a weekend or holiday     SELECT  TotalBusinessDays              SELECT CASE             --Saturday OR Sunday             WHEN  DayOfWeek IN  6 7  THEN 0             --New Year s Day             WHEN  Month   1 AND  Day   1 THEN 0             --MLK Jr  Day             WHEN  Month   1 AND  DayOfWeek   1 AND  DayOccurrence   3 THEN 0             --G  Washington s Birthday             WHEN  Month   2 AND  DayOfWeek   1 AND  DayOccurrence   3 THEN 0             --Memorial Day             WHEN  Month   5 AND  DayOfWeek   1 AND  Day BETWEEN 25 AND 31 THEN 0             --Independence Day             WHEN  Month   7 AND  Day   4 THEN 0             --Labor Day             WHEN  Month   9 AND  DayOfWeek   1 AND  DayOccurrence   1 THEN 0             --Columbus Day             WHEN  Month   10 AND  DayOfWeek   1 AND  DayOccurrence   2 THEN 0             --Veterans Day             WHEN  Month   11 AND  Day   11 THEN 0             --Thanksgiving             WHEN  Month   11 AND  DayOfWeek   4 AND  DayOccurrence   4 THEN 0             --Christmas             WHEN  Month   12 AND  Day   25 THEN 0             ELSE 1             END AS Result        SET  TestDate   DATEADD dd  1   TestDate   END  RETURN  TotalBusinessDays  END

User · Answer

In Calculating Work Days you can find a good article about this subject  but as you can see it is not that advanced   --Changing current database to the Master database allows function to be shared by everyone  USE MASTER GO --If the function already exists  drop it  IF EXISTS       SELECT       FROM dbo SYSOBJECTS     WHERE ID   OBJECT ID N  dbo   fn WorkDays        AND XType IN  N FN   N IF   N TF     DROP FUNCTION  dbo   fn WorkDays  GO  CREATE FUNCTION dbo fn WorkDays --Presets --Define the input parameters  OK if reversed by mistake          StartDate DATETIME       EndDate   DATETIME   NULL -- EndDate replaced by  StartDate when DEFAULTed    --Define the output data type  RETURNS INT  AS --Calculate the RETURN of the function  BEGIN     --Declare local variables     --Temporarily holds  EndDate during date reversal      DECLARE  Swap DATETIME      --If the Start Date is null  return a NULL and exit      IF  StartDate IS NULL         RETURN NULL      --If the End Date is null  populate with Start Date value so will have two dates  required by DATEDIFF below        IF  EndDate IS NULL         SELECT  EndDate    StartDate      --Strip the time element from both dates  just to be safe  by converting to whole days and back to a date      --Usually faster than CONVERT      --0 is a date  01 01 1900 00 00 00 000       SELECT  StartDate   DATEADD dd DATEDIFF dd 0  StartDate   0                EndDate     DATEADD dd DATEDIFF dd 0  EndDate     0       --If the inputs are in the wrong order  reverse them       IF  StartDate  gt   EndDate         SELECT  Swap         EndDate                  EndDate      StartDate                  StartDate    Swap      --Calculate and return the number of workdays using the input parameters      --This is the meat of the function      --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes       RETURN           SELECT         --Start with total number of days including weekends          DATEDIFF dd  StartDate   EndDate  1          --Subtact 2 days for each full weekend         - DATEDIFF wk  StartDate   EndDate  2          --If StartDate is a Sunday  Subtract 1         - CASE WHEN DATENAME dw   StartDate     Sunday              THEN 1             ELSE 0         END          --If EndDate is a Saturday  Subtract 1         - CASE WHEN DATENAME dw   EndDate     Saturday              THEN 1             ELSE 0         END                END GO   If you need to use a custom calendar  you might need to add some checks and some parameters  Hopefully it will provide a good starting point

User · Answer

DECLARE  StartDate datetime  EndDate datetime  select  StartDate  3 2 2010    EndDate  3 7 2010   DECLARE  TotalDays INT  WorkDays INT  DECLARE  ReducedDayswithEndDate INT  DECLARE  WeekPart INT  DECLARE  DatePart INT  SET  TotalDays  DATEDIFF day   StartDate   EndDate   1  SELECT  ReducedDayswithEndDate   CASE DATENAME weekday   EndDate      WHEN  Saturday  THEN 1     WHEN  Sunday  THEN 2     ELSE 0 END  SET  TotalDays  TotalDays- ReducedDayswithEndDate  SET  WeekPart  TotalDays 7   SET  DatePart  TotalDays 7   SET  WorkDays   WeekPart 5   DatePart  SELECT  WorkDays

User · Answer

All Credit to Bogdan Maxim  amp  Peter Mortensen  This is their post  I just added holidays to the function  This assumes you have a table  tblHolidays  with a datetime field  HolDate      --Changing current database to the Master database allows function to be shared by everyone  USE MASTER GO --If the function already exists  drop it  IF EXISTS       SELECT       FROM dbo SYSOBJECTS     WHERE ID   OBJECT ID N  dbo   fn WorkDays        AND XType IN  N FN   N IF   N TF      DROP FUNCTION  dbo   fn WorkDays  GO  CREATE FUNCTION dbo fn WorkDays --Presets --Define the input parameters  OK if reversed by mistake          StartDate DATETIME       EndDate   DATETIME   NULL -- EndDate replaced by  StartDate when DEFAULTed    --Define the output data type  RETURNS INT  AS --Calculate the RETURN of the function  BEGIN     --Declare local variables     --Temporarily holds  EndDate during date reversal      DECLARE  Swap DATETIME      --If the Start Date is null  return a NULL and exit      IF  StartDate IS NULL         RETURN NULL      --If the End Date is null  populate with Start Date value so will have two dates  required by DATEDIFF below       IF  EndDate IS NULL         SELECT  EndDate    StartDate      --Strip the time element from both dates  just to be safe  by converting to whole days and back to a date      --Usually faster than CONVERT      --0 is a date  01 01 1900 00 00 00 000      SELECT  StartDate   DATEADD dd DATEDIFF dd 0  StartDate   0                EndDate     DATEADD dd DATEDIFF dd 0  EndDate     0       --If the inputs are in the wrong order  reverse them      IF  StartDate  gt   EndDate         SELECT  Swap         EndDate                  EndDate      StartDate                  StartDate    Swap      --Calculate and return the number of workdays using the input parameters      --This is the meat of the function      --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes      RETURN           SELECT         --Start with total number of days including weekends          DATEDIFF dd  StartDate   EndDate  1          --Subtact 2 days for each full weekend         - DATEDIFF wk  StartDate   EndDate  2          --If StartDate is a Sunday  Subtract 1         - CASE WHEN DATENAME dw   StartDate     Sunday              THEN 1             ELSE 0         END          --If EndDate is a Saturday  Subtract 1         - CASE WHEN DATENAME dw   EndDate     Saturday              THEN 1             ELSE 0         END          --Subtract all holidays         - Select Count    from  DB04 DB04   Gateway   dbo   tblHolidays            where   HolDate  between  StartDate and  EndDate                 END   GO -- Test Script    declare  EndDate datetime  dateadd m 2 getdate    print  EndDate select   Master   dbo   fn WorkDays   getdate     EndDate

User · Answer

DECLARE  TotalDays INT  WorkDays INT  DECLARE  ReducedDayswithEndDate INT  DECLARE  WeekPart INT  DECLARE  DatePart INT   SET  TotalDays  DATEDIFF day   StartDate   EndDate   1  SELECT  ReducedDayswithEndDate   CASE DATENAME weekday   EndDate    WHEN  Saturday  THEN 1   WHEN  Sunday  THEN 2   ELSE 0 END   SET  TotalDays  TotalDays- ReducedDayswithEndDate  SET  WeekPart  TotalDays 7   SET  DatePart  TotalDays 7   SET  WorkDays   WeekPart 5   DatePart   RETURN  WorkDays

User · Answer

I know this is an old question but I needed a formula for workdays excluding the start date since I have several items and need the days to accumulate correctly   None of the non-iterative answers worked for me   I used a defintion like     Number of times midnight to monday  tuesday  wednesday  thursday and friday is passed    others might count midnight to saturday instead of monday   I ended up with this formula  SELECT DATEDIFF day   StartDate   EndDate     all midnights passed         - DATEDIFF week   StartDate   EndDate     remove sunday midnights         - DATEDIFF week  DATEADD day  1   StartDate   DATEADD day  1   EndDate      remove saturday midnights

User · Answer

In Calculating Work Days you can find a good article about this subject  but as you can see it is not that advanced   --Changing current database to the Master database allows function to be shared by everyone  USE MASTER GO --If the function already exists  drop it  IF EXISTS       SELECT       FROM dbo SYSOBJECTS     WHERE ID   OBJECT ID N  dbo   fn WorkDays        AND XType IN  N FN   N IF   N TF     DROP FUNCTION  dbo   fn WorkDays  GO  CREATE FUNCTION dbo fn WorkDays --Presets --Define the input parameters  OK if reversed by mistake          StartDate DATETIME       EndDate   DATETIME   NULL -- EndDate replaced by  StartDate when DEFAULTed    --Define the output data type  RETURNS INT  AS --Calculate the RETURN of the function  BEGIN     --Declare local variables     --Temporarily holds  EndDate during date reversal      DECLARE  Swap DATETIME      --If the Start Date is null  return a NULL and exit      IF  StartDate IS NULL         RETURN NULL      --If the End Date is null  populate with Start Date value so will have two dates  required by DATEDIFF below        IF  EndDate IS NULL         SELECT  EndDate    StartDate      --Strip the time element from both dates  just to be safe  by converting to whole days and back to a date      --Usually faster than CONVERT      --0 is a date  01 01 1900 00 00 00 000       SELECT  StartDate   DATEADD dd DATEDIFF dd 0  StartDate   0                EndDate     DATEADD dd DATEDIFF dd 0  EndDate     0       --If the inputs are in the wrong order  reverse them       IF  StartDate  gt   EndDate         SELECT  Swap         EndDate                  EndDate      StartDate                  StartDate    Swap      --Calculate and return the number of workdays using the input parameters      --This is the meat of the function      --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes       RETURN           SELECT         --Start with total number of days including weekends          DATEDIFF dd  StartDate   EndDate  1          --Subtact 2 days for each full weekend         - DATEDIFF wk  StartDate   EndDate  2          --If StartDate is a Sunday  Subtract 1         - CASE WHEN DATENAME dw   StartDate     Sunday              THEN 1             ELSE 0         END          --If EndDate is a Saturday  Subtract 1         - CASE WHEN DATENAME dw   EndDate     Saturday              THEN 1             ELSE 0         END                END GO   If you need to use a custom calendar  you might need to add some checks and some parameters  Hopefully it will provide a good starting point

User · Answer

Create function like   CREATE FUNCTION dbo fn WorkDays  StartDate DATETIME   EndDate DATETIME  NULL   RETURNS INT  AS BEGIN        DECLARE  Days int        SET  Days   0         IF  EndDate   NULL               SET  EndDate   EOMONTH  StartDate  --last date of the month         WHILE DATEDIFF dd  StartDate  EndDate   gt   0        BEGIN               IF DATENAME dw   StartDate   lt  gt   Saturday                        and DATENAME dw   StartDate   lt  gt   Sunday                        and Not   Day  StartDate    1 And Month  StartDate    1   --New Year s Day                       and Not   Day  StartDate    4 And Month  StartDate    7   --Independence Day                BEGIN                      SET  Days    Days   1               END                SET  StartDate   DATEADD dd 1  StartDate         END         RETURN   Days END   You can call the function like    select dbo fn WorkDays  1 1 2016    9 25 2016     Or like   select dbo fn WorkDays StartDate  EndDate   from table1

User · Answer

None of the functions above work for the same week or deal with holidays   I wrote this  create FUNCTION  dbo   ShiftHolidayToWorkday   date date  RETURNS date AS BEGIN     IF DATENAME  dw   Date      Saturday          SET  Date   DATEADD day  - 1   Date       ELSE IF DATENAME  dw   Date      Sunday          SET  Date   DATEADD day  1   Date       RETURN  date END GO  create FUNCTION  dbo   GetHoliday   date date  RETURNS varchar 50  AS BEGIN     declare  s varchar 50       SELECT  s   CASE         WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year        -01-01      date THEN  New Year          WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year  1     -01-01      date THEN  New Year          WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year        -07-04      date THEN  Independence Day          WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year        -12-25      date THEN  Christmas Day          --WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year      -12-31      date THEN  New Years Eve          --WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year      -11-11      date THEN  Veteran  s Day           WHEN  Month    1  AND  DayOfMonth  BETWEEN 15 AND 21 AND  DayName     Monday  THEN  Martin Luther King Day          WHEN  Month    5  AND  DayOfMonth   gt   25             AND  DayName     Monday  THEN  Memorial Day          WHEN  Month    9  AND  DayOfMonth   lt   7              AND  DayName     Monday  THEN  Labor Day          WHEN  Month    11 AND  DayOfMonth  BETWEEN 22 AND 28 AND  DayName     Thursday  THEN  Thanksgiving Day          WHEN  Month    11 AND  DayOfMonth  BETWEEN 23 AND 29 AND  DayName     Friday  THEN  Day After Thanksgiving          ELSE NULL END     FROM           SELECT              Year    YEAR  date                Month    MONTH  date                DayOfMonth    DAY  date                DayName      DATENAME weekday  date        c      RETURN  s END GO  create FUNCTION  dbo  GetHolidays  year int  RETURNS TABLE  AS RETURN         select dt  dbo GetHoliday dt  as Holiday     from           select dateadd day  number  convert varchar  year     -01-01   dt         from master  spt values          where type  p             d     where year dt     year and dbo GetHoliday dt  is not null    create proc UpdateHolidaysTable as  if not exists select TABLE NAME from INFORMATION SCHEMA TABLES where TABLE NAME    Holidays       create table Holidays dt date primary key clustered  Holiday varchar 50    declare  year int set  year   1990  while  year  lt  year GetDate      20 begin     insert into Holidays dt  Holiday      select a dt  a Holiday     from dbo GetHolidays  year  a         left join Holidays b on b dt   a dt     where b dt is null      set  year    year   1 end  create FUNCTION  dbo   GetWorkDays   StartDate DATE   NULL   EndDate DATE   NULL  RETURNS INT  AS BEGIN     IF  StartDate IS NULL OR  EndDate IS NULL         RETURN  0      IF  StartDate  gt    EndDate          RETURN  0      DECLARE  Days int     SET  Days   0      IF year  StartDate    100   datepart week   StartDate    year  EndDate    100   datepart week   EndDate           --same week         select  Days    DATEDIFF dd   StartDate   EndDate         -  CASE WHEN DATENAME dw   StartDate     Sunday  THEN 1 ELSE 0 END        -  CASE WHEN DATENAME dw   EndDate     Saturday  THEN 1 ELSE 0 END        -  select count    from Holidays where dt between  StartDate and  EndDate      ELSE         --diff weeks         select  Days    DATEDIFF dd   StartDate   EndDate    1        -  DATEDIFF wk   StartDate   EndDate    2        -  CASE WHEN DATENAME dw   StartDate     Sunday  THEN 1 ELSE 0 END        -  CASE WHEN DATENAME dw   EndDate     Saturday  THEN 1 ELSE 0 END        -  select count    from Holidays where dt between  StartDate and  EndDate        RETURN   Days END

User · Answer

That s working for me  in my country on Saturday and Sunday are non-working days   For me is important the time of  StartDate and  EndDate   CREATE FUNCTION  dbo   fnGetCountWorkingBusinessDays         StartDate as DATETIME       EndDate as DATETIME   RETURNS INT AS BEGIN     DECLARE  res int  SET  StartDate   CASE      WHEN DATENAME dw   StartDate     Saturday  THEN DATEADD dd  2  DATEDIFF dd  0   StartDate       WHEN DATENAME dw   StartDate     Sunday  THEN DATEADD dd  1  DATEDIFF dd  0   StartDate       ELSE  StartDate END  SET  EndDate   CASE      WHEN DATENAME dw   EndDate     Saturday  THEN DATEADD dd  0  DATEDIFF dd  0   EndDate       WHEN DATENAME dw   EndDate     Sunday  THEN DATEADD dd  -1  DATEDIFF dd  0   EndDate       ELSE  EndDate END   SET  res        DATEDIFF hour   StartDate   EndDate    24    -  DATEDIFF wk   StartDate   EndDate    2   SET  res   CASE WHEN  res  lt  0 THEN 0 ELSE  res END      RETURN  res END  GO

User · Answer

DECLARE  StartDate datetime  EndDate datetime  select  StartDate  3 2 2010    EndDate  3 7 2010   DECLARE  TotalDays INT  WorkDays INT  DECLARE  ReducedDayswithEndDate INT  DECLARE  WeekPart INT  DECLARE  DatePart INT  SET  TotalDays  DATEDIFF day   StartDate   EndDate   1  SELECT  ReducedDayswithEndDate   CASE DATENAME weekday   EndDate      WHEN  Saturday  THEN 1     WHEN  Sunday  THEN 2     ELSE 0 END  SET  TotalDays  TotalDays- ReducedDayswithEndDate  SET  WeekPart  TotalDays 7   SET  DatePart  TotalDays 7   SET  WorkDays   WeekPart 5   DatePart  SELECT  WorkDays

User · Answer

My version of the accepted answer as a function using DATEPART  so I don t have to do a string comparison on the line with   DATENAME dw   StartDate     Sunday    Anyway  here s my business datediff function  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  CREATE FUNCTION BDATEDIFF        startdate as DATETIME       enddate as DATETIME   RETURNS INT AS BEGIN     DECLARE  res int  SET  res    DATEDIFF dd   startdate   enddate    1      - DATEDIFF wk   startdate   enddate    2      - CASE WHEN DATEPART dw   startdate    1 THEN 1 ELSE 0 END      - CASE WHEN DATEPART dw   enddate    7 THEN 1 ELSE 0 END       RETURN  res END GO

User · Answer

I m a few points shy of commenting privileges   If you decide to forgo the  1 day in CMS s elegant solution  note that if your start date and end date are in the same weekend  you get a negative answer  Ie   2008 10 26 to 2008 10 26 returns -1   my rather simplistic solution   select  Result      CMS s answer    if    Result  lt  0          select  Result   0     RETURN  Result      which also sets all erroneous posts with start date after end date to zero  Something you may or may not be looking for

User · Answer

This is basically CMS s answer without the reliance on a particular language setting  And since we re shooting for generic  that means it should work for all   datefirst settings as well   datediff day   lt start gt    lt end gt     1 - datediff week   lt start gt    lt end gt     2        if start is a Sunday  adjust by -1        case when datepart weekday   lt start gt     8 -   datefirst then -1 else 0 end        if end is a Saturday  adjust by -1        case when datepart weekday   lt end gt      13 -   datefirst    7   1 then -1 else 0 end   datediff week       always uses a Saturday-to-Sunday boundary for weeks  so that expression is deterministic and doesn t need to be modified  as long as our definition of weekdays is consistently Monday through Friday   Day numbering does vary according to the   datefirst setting and the modified calculations handle this correction with the small complication of some modular arithmetic   A cleaner way to deal with the Saturday Sunday thing is to translate the dates prior to extracting a day of week value  After shifting  the values will be back in line with a fixed  and probably more familiar  numbering that starts with 1 on Sunday and ends with 7 on Saturday   datediff day   lt start gt    lt end gt     1 - datediff week   lt start gt    lt end gt     2     case when datepart weekday  dateadd day    datefirst   lt start gt      1 then -1 else 0 end     case when datepart weekday  dateadd day    datefirst   lt end gt        7 then -1 else 0 end   I ve tracked this form of the solution back at least as far as 2002 and an Itzik Ben-Gan article   https   technet microsoft com en-us library aa175781 v sql 80  aspx  Though it needed a small tweak since newer date types don t allow date arithmetic  it is otherwise identical   EDIT  I added back the  1 that had somehow been left off  It s also worth noting that this method always counts the start and end days  It also assumes that the end date is on or after the start date

User · Answer

I know this is an old question but I needed a formula for workdays excluding the start date since I have several items and need the days to accumulate correctly   None of the non-iterative answers worked for me   I used a defintion like     Number of times midnight to monday  tuesday  wednesday  thursday and friday is passed    others might count midnight to saturday instead of monday   I ended up with this formula  SELECT DATEDIFF day   StartDate   EndDate     all midnights passed         - DATEDIFF week   StartDate   EndDate     remove sunday midnights         - DATEDIFF week  DATEADD day  1   StartDate   DATEADD day  1   EndDate      remove saturday midnights

User · Answer

That s working for me  in my country on Saturday and Sunday are non-working days   For me is important the time of  StartDate and  EndDate   CREATE FUNCTION  dbo   fnGetCountWorkingBusinessDays         StartDate as DATETIME       EndDate as DATETIME   RETURNS INT AS BEGIN     DECLARE  res int  SET  StartDate   CASE      WHEN DATENAME dw   StartDate     Saturday  THEN DATEADD dd  2  DATEDIFF dd  0   StartDate       WHEN DATENAME dw   StartDate     Sunday  THEN DATEADD dd  1  DATEDIFF dd  0   StartDate       ELSE  StartDate END  SET  EndDate   CASE      WHEN DATENAME dw   EndDate     Saturday  THEN DATEADD dd  0  DATEDIFF dd  0   EndDate       WHEN DATENAME dw   EndDate     Sunday  THEN DATEADD dd  -1  DATEDIFF dd  0   EndDate       ELSE  EndDate END   SET  res        DATEDIFF hour   StartDate   EndDate    24    -  DATEDIFF wk   StartDate   EndDate    2   SET  res   CASE WHEN  res  lt  0 THEN 0 ELSE  res END      RETURN  res END  GO

User · Answer

Create function like   CREATE FUNCTION dbo fn WorkDays  StartDate DATETIME   EndDate DATETIME  NULL   RETURNS INT  AS BEGIN        DECLARE  Days int        SET  Days   0         IF  EndDate   NULL               SET  EndDate   EOMONTH  StartDate  --last date of the month         WHILE DATEDIFF dd  StartDate  EndDate   gt   0        BEGIN               IF DATENAME dw   StartDate   lt  gt   Saturday                        and DATENAME dw   StartDate   lt  gt   Sunday                        and Not   Day  StartDate    1 And Month  StartDate    1   --New Year s Day                       and Not   Day  StartDate    4 And Month  StartDate    7   --Independence Day                BEGIN                      SET  Days    Days   1               END                SET  StartDate   DATEADD dd 1  StartDate         END         RETURN   Days END   You can call the function like    select dbo fn WorkDays  1 1 2016    9 25 2016     Or like   select dbo fn WorkDays StartDate  EndDate   from table1

User · Answer

Create Function dbo DateDiff WeekDays     StartDate  DateTime   EndDate    DateTime   Returns Int As  Begin     Declare  Result Int   0  While    StartDate  lt    EndDate Begin      If DateName DW   StartDate  not in   Saturday   Sunday           Begin             Set  Result    Result  1         End         Set  StartDate   DateAdd Day   1   StartDate  End  Return  Result   End

User · Answer

In Calculating Work Days you can find a good article about this subject  but as you can see it is not that advanced   --Changing current database to the Master database allows function to be shared by everyone  USE MASTER GO --If the function already exists  drop it  IF EXISTS       SELECT       FROM dbo SYSOBJECTS     WHERE ID   OBJECT ID N  dbo   fn WorkDays        AND XType IN  N FN   N IF   N TF     DROP FUNCTION  dbo   fn WorkDays  GO  CREATE FUNCTION dbo fn WorkDays --Presets --Define the input parameters  OK if reversed by mistake          StartDate DATETIME       EndDate   DATETIME   NULL -- EndDate replaced by  StartDate when DEFAULTed    --Define the output data type  RETURNS INT  AS --Calculate the RETURN of the function  BEGIN     --Declare local variables     --Temporarily holds  EndDate during date reversal      DECLARE  Swap DATETIME      --If the Start Date is null  return a NULL and exit      IF  StartDate IS NULL         RETURN NULL      --If the End Date is null  populate with Start Date value so will have two dates  required by DATEDIFF below        IF  EndDate IS NULL         SELECT  EndDate    StartDate      --Strip the time element from both dates  just to be safe  by converting to whole days and back to a date      --Usually faster than CONVERT      --0 is a date  01 01 1900 00 00 00 000       SELECT  StartDate   DATEADD dd DATEDIFF dd 0  StartDate   0                EndDate     DATEADD dd DATEDIFF dd 0  EndDate     0       --If the inputs are in the wrong order  reverse them       IF  StartDate  gt   EndDate         SELECT  Swap         EndDate                  EndDate      StartDate                  StartDate    Swap      --Calculate and return the number of workdays using the input parameters      --This is the meat of the function      --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes       RETURN           SELECT         --Start with total number of days including weekends          DATEDIFF dd  StartDate   EndDate  1          --Subtact 2 days for each full weekend         - DATEDIFF wk  StartDate   EndDate  2          --If StartDate is a Sunday  Subtract 1         - CASE WHEN DATENAME dw   StartDate     Sunday              THEN 1             ELSE 0         END          --If EndDate is a Saturday  Subtract 1         - CASE WHEN DATENAME dw   EndDate     Saturday              THEN 1             ELSE 0         END                END GO   If you need to use a custom calendar  you might need to add some checks and some parameters  Hopefully it will provide a good starting point

User · Answer

I found the below TSQL a fairly elegant solution  I don t have permissions to run functions   I found the DATEDIFF ignores DATEFIRST and I wanted my first day of the week to be a Monday  I also wanted the first working day to be set a zero and if it falls on a weekend Monday will be a zero  This may help someone who has a slightly different requirement      It does not handle bank holidays  SET DATEFIRST 1 SELECT   DATEDIFF DD    StartDate    EndDate            - DATEDIFF wk    StartDate    EndDate            - DATEDIFF wk  DATEADD dd -  DATEFIRST  StartDate    DATEADD dd -  DATEFIRST  EndDate     AS  WorkingDays   FROM   Your Table

User · Answer

All Credit to Bogdan Maxim  amp  Peter Mortensen  This is their post  I just added holidays to the function  This assumes you have a table  tblHolidays  with a datetime field  HolDate      --Changing current database to the Master database allows function to be shared by everyone  USE MASTER GO --If the function already exists  drop it  IF EXISTS       SELECT       FROM dbo SYSOBJECTS     WHERE ID   OBJECT ID N  dbo   fn WorkDays        AND XType IN  N FN   N IF   N TF      DROP FUNCTION  dbo   fn WorkDays  GO  CREATE FUNCTION dbo fn WorkDays --Presets --Define the input parameters  OK if reversed by mistake          StartDate DATETIME       EndDate   DATETIME   NULL -- EndDate replaced by  StartDate when DEFAULTed    --Define the output data type  RETURNS INT  AS --Calculate the RETURN of the function  BEGIN     --Declare local variables     --Temporarily holds  EndDate during date reversal      DECLARE  Swap DATETIME      --If the Start Date is null  return a NULL and exit      IF  StartDate IS NULL         RETURN NULL      --If the End Date is null  populate with Start Date value so will have two dates  required by DATEDIFF below       IF  EndDate IS NULL         SELECT  EndDate    StartDate      --Strip the time element from both dates  just to be safe  by converting to whole days and back to a date      --Usually faster than CONVERT      --0 is a date  01 01 1900 00 00 00 000      SELECT  StartDate   DATEADD dd DATEDIFF dd 0  StartDate   0                EndDate     DATEADD dd DATEDIFF dd 0  EndDate     0       --If the inputs are in the wrong order  reverse them      IF  StartDate  gt   EndDate         SELECT  Swap         EndDate                  EndDate      StartDate                  StartDate    Swap      --Calculate and return the number of workdays using the input parameters      --This is the meat of the function      --This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes      RETURN           SELECT         --Start with total number of days including weekends          DATEDIFF dd  StartDate   EndDate  1          --Subtact 2 days for each full weekend         - DATEDIFF wk  StartDate   EndDate  2          --If StartDate is a Sunday  Subtract 1         - CASE WHEN DATENAME dw   StartDate     Sunday              THEN 1             ELSE 0         END          --If EndDate is a Saturday  Subtract 1         - CASE WHEN DATENAME dw   EndDate     Saturday              THEN 1             ELSE 0         END          --Subtract all holidays         - Select Count    from  DB04 DB04   Gateway   dbo   tblHolidays            where   HolDate  between  StartDate and  EndDate                 END   GO -- Test Script    declare  EndDate datetime  dateadd m 2 getdate    print  EndDate select   Master   dbo   fn WorkDays   getdate     EndDate

User · Answer

Here is a version that works well  I think    Holiday table contains Holiday date columns that contains holidays your company observe   DECLARE  RAWDAYS INT     SELECT  RAWDAYS    DATEDIFF day   StartDate   EndDate  -- 1                     -  2   DATEDIFF  week   StartDate   EndDate                           CASE WHEN DATENAME dw   StartDate     Saturday  THEN 1 ELSE 0 END                     - CASE WHEN DATENAME dw   EndDate     Saturday  THEN 1 ELSE 0 END      SELECT   RAWDAYS - COUNT          FROM HOLIDAY NumberOfBusinessDays     WHERE  Holiday Date  BETWEEN  StartDate 1 AND  EndDate

User · Answer

Another approach to calculating working days is to use a  WHILE  loop which basically iterates through a date range and increment it by 1 whenever days are found to be within Monday     Friday  The complete script for calculating working days using the  WHILE  loop is shown below   CREATE FUNCTION  dbo   fn GetTotalWorkingDaysUsingLoop    DateFrom DATE   DateTo     DATE   RETURNS INT AS          BEGIN                  DECLARE  TotWorkingDays INT  0                   WHILE  DateFrom  lt    DateTo                          BEGIN                                  IF DATENAME WEEKDAY   DateFrom  IN  Monday    Tuesday    Wednesday    Thursday    Friday                                            BEGIN                                                  SET  TotWorkingDays    TotWorkingDays   1                                   END                                   SET  DateFrom   DATEADD DAY  1   DateFrom                            END                   RETURN  TotWorkingDays           END  GO   Although the  WHILE  loop option is cleaner and uses less lines of code  it has the potential of being a performance bottleneck in your environment particularly when your date range spans across several years   You can see more methods on how to calculate work days and hours in this article  https   www sqlshack com how-to-calculate-work-days-and-hours-in-sql-server

User · Answer

CREATE FUNCTION x        StartDate DATETIME       EndDate DATETIME   RETURNS INT AS BEGIN     DECLARE  Teller INT      SET  StartDate   DATEADD dd 1  StartDate       SET  Teller   0     IF DATEDIFF dd  StartDate  EndDate   lt   0     BEGIN         SET  Teller   0      END     ELSE     BEGIN         WHILE             DATEDIFF dd  StartDate  EndDate   gt   0         BEGIN             IF DATEPART dw  StartDate   lt  6             BEGIN                 SET  Teller    Teller   1             END             SET  StartDate   DATEADD dd 1  StartDate          END     END     RETURN  Teller END

User · Answer

Create Function dbo DateDiff WeekDays     StartDate  DateTime   EndDate    DateTime   Returns Int As  Begin     Declare  Result Int   0  While    StartDate  lt    EndDate Begin      If DateName DW   StartDate  not in   Saturday   Sunday           Begin             Set  Result    Result  1         End         Set  StartDate   DateAdd Day   1   StartDate  End  Return  Result   End

User · Answer

None of the functions above work for the same week or deal with holidays   I wrote this  create FUNCTION  dbo   ShiftHolidayToWorkday   date date  RETURNS date AS BEGIN     IF DATENAME  dw   Date      Saturday          SET  Date   DATEADD day  - 1   Date       ELSE IF DATENAME  dw   Date      Sunday          SET  Date   DATEADD day  1   Date       RETURN  date END GO  create FUNCTION  dbo   GetHoliday   date date  RETURNS varchar 50  AS BEGIN     declare  s varchar 50       SELECT  s   CASE         WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year        -01-01      date THEN  New Year          WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year  1     -01-01      date THEN  New Year          WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year        -07-04      date THEN  Independence Day          WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year        -12-25      date THEN  Christmas Day          --WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year      -12-31      date THEN  New Years Eve          --WHEN dbo ShiftHolidayToWorkday CONVERT varchar   Year      -11-11      date THEN  Veteran  s Day           WHEN  Month    1  AND  DayOfMonth  BETWEEN 15 AND 21 AND  DayName     Monday  THEN  Martin Luther King Day          WHEN  Month    5  AND  DayOfMonth   gt   25             AND  DayName     Monday  THEN  Memorial Day          WHEN  Month    9  AND  DayOfMonth   lt   7              AND  DayName     Monday  THEN  Labor Day          WHEN  Month    11 AND  DayOfMonth  BETWEEN 22 AND 28 AND  DayName     Thursday  THEN  Thanksgiving Day          WHEN  Month    11 AND  DayOfMonth  BETWEEN 23 AND 29 AND  DayName     Friday  THEN  Day After Thanksgiving          ELSE NULL END     FROM           SELECT              Year    YEAR  date                Month    MONTH  date                DayOfMonth    DAY  date                DayName      DATENAME weekday  date        c      RETURN  s END GO  create FUNCTION  dbo  GetHolidays  year int  RETURNS TABLE  AS RETURN         select dt  dbo GetHoliday dt  as Holiday     from           select dateadd day  number  convert varchar  year     -01-01   dt         from master  spt values          where type  p             d     where year dt     year and dbo GetHoliday dt  is not null    create proc UpdateHolidaysTable as  if not exists select TABLE NAME from INFORMATION SCHEMA TABLES where TABLE NAME    Holidays       create table Holidays dt date primary key clustered  Holiday varchar 50    declare  year int set  year   1990  while  year  lt  year GetDate      20 begin     insert into Holidays dt  Holiday      select a dt  a Holiday     from dbo GetHolidays  year  a         left join Holidays b on b dt   a dt     where b dt is null      set  year    year   1 end  create FUNCTION  dbo   GetWorkDays   StartDate DATE   NULL   EndDate DATE   NULL  RETURNS INT  AS BEGIN     IF  StartDate IS NULL OR  EndDate IS NULL         RETURN  0      IF  StartDate  gt    EndDate          RETURN  0      DECLARE  Days int     SET  Days   0      IF year  StartDate    100   datepart week   StartDate    year  EndDate    100   datepart week   EndDate           --same week         select  Days    DATEDIFF dd   StartDate   EndDate         -  CASE WHEN DATENAME dw   StartDate     Sunday  THEN 1 ELSE 0 END        -  CASE WHEN DATENAME dw   EndDate     Saturday  THEN 1 ELSE 0 END        -  select count    from Holidays where dt between  StartDate and  EndDate      ELSE         --diff weeks         select  Days    DATEDIFF dd   StartDate   EndDate    1        -  DATEDIFF wk   StartDate   EndDate    2        -  CASE WHEN DATENAME dw   StartDate     Sunday  THEN 1 ELSE 0 END        -  CASE WHEN DATENAME dw   EndDate     Saturday  THEN 1 ELSE 0 END        -  select count    from Holidays where dt between  StartDate and  EndDate        RETURN   Days END

User · Answer

For difference between dates including holidays I went this way   1  Table with Holidays       CREATE TABLE  dbo   Holiday    Id   int  IDENTITY 1 1  NOT NULL   Name   nvarchar  50  NULL   Date   datetime  NOT NULL    2  I had my plannings Table like this and wanted to fill column Work Days which was empty       CREATE TABLE  dbo   Plan Phase    Id   int  IDENTITY 1 1  NOT NULL   Id Plan   int  NOT NULL   Id Phase   int  NOT NULL   Start Date   datetime  NULL   End Date   datetime  NULL   Work Days   int  NULL    3  So in order to get  Work Days  to later fill in my column just had to   SELECT Start Date  End Date    DATEDIFF dd  Start Date  End Date    1  - DATEDIFF wk  Start Date  End Date    2  - SELECT COUNT    From Holiday Where Date   gt   Start Date AND Date  lt   End Date  - CASE WHEN DATENAME dw  Start Date     Sunday  THEN 1 ELSE 0 END  - CASE WHEN DATENAME dw  End Date     Saturday  THEN 1 ELSE 0 END  - CASE WHEN  SELECT COUNT    From Holiday Where Start Date    Date   gt  0 THEN 1 ELSE 0 END  - CASE WHEN  SELECT COUNT    From Holiday Where End Date    Date   gt  0 THEN 1 ELSE 0 END  AS Work Days from Plan Phase   Hope that I could help   Cheers

User · Answer

I took the various examples here  but in my particular situation we have a  PromisedDate for delivery and a  ReceivedDate for the actual receipt of the item   When an item was received before the  PromisedDate  the calculations were not totaling correctly unless I ordered the dates passed into the function by calendar order   Not wanting to check the dates every time  I changed the function to handle this for me   Create FUNCTION  dbo   fnGetBusinessDays      PromiseDate date    ReceivedDate date   RETURNS integer AS BEGIN  DECLARE  days integer   SELECT  days        Case when  PromiseDate  gt   ReceivedDate Then         DATEDIFF d  PromiseDate  ReceivedDate             ABS DATEDIFF wk  PromiseDate  ReceivedDate     2           CASE              WHEN DATENAME dw   PromiseDate   lt  gt   Saturday  AND DATENAME dw   ReceivedDate     Saturday  THEN 1              WHEN DATENAME dw   PromiseDate     Saturday  AND DATENAME dw   ReceivedDate   lt  gt   Saturday  THEN -1              ELSE 0         END            Select COUNT    FROM CompanyHolidays              WHERE HolidayDate BETWEEN  ReceivedDate AND  PromiseDate              AND DATENAME dw  HolidayDate   lt  gt   Saturday  AND DATENAME dw  HolidayDate   lt  gt   Sunday       Else         DATEDIFF d  PromiseDate  ReceivedDate   -         ABS DATEDIFF wk  PromiseDate  ReceivedDate     2  -             CASE                  WHEN DATENAME dw   PromiseDate   lt  gt   Saturday  AND DATENAME dw   ReceivedDate     Saturday  THEN 1                  WHEN DATENAME dw   PromiseDate     Saturday  AND DATENAME dw   ReceivedDate   lt  gt   Saturday  THEN -1                  ELSE 0             END -          Select COUNT    FROM CompanyHolidays              WHERE HolidayDate BETWEEN  PromiseDate and  ReceivedDate              AND DATENAME dw  HolidayDate   lt  gt   Saturday  AND DATENAME dw  HolidayDate   lt  gt   Sunday       End    RETURN   days   END

User · Answer

Another approach to calculating working days is to use a  WHILE  loop which basically iterates through a date range and increment it by 1 whenever days are found to be within Monday     Friday  The complete script for calculating working days using the  WHILE  loop is shown below   CREATE FUNCTION  dbo   fn GetTotalWorkingDaysUsingLoop    DateFrom DATE   DateTo     DATE   RETURNS INT AS          BEGIN                  DECLARE  TotWorkingDays INT  0                   WHILE  DateFrom  lt    DateTo                          BEGIN                                  IF DATENAME WEEKDAY   DateFrom  IN  Monday    Tuesday    Wednesday    Thursday    Friday                                            BEGIN                                                  SET  TotWorkingDays    TotWorkingDays   1                                   END                                   SET  DateFrom   DATEADD DAY  1   DateFrom                            END                   RETURN  TotWorkingDays           END  GO   Although the  WHILE  loop option is cleaner and uses less lines of code  it has the potential of being a performance bottleneck in your environment particularly when your date range spans across several years   You can see more methods on how to calculate work days and hours in this article  https   www sqlshack com how-to-calculate-work-days-and-hours-in-sql-server

User · Answer

Using a date table       DECLARE           StartDate date    2014-01-01            EndDate date    2014-01-31        SELECT          COUNT    As NumberOfWeekDays     FROM dbo Calendar     WHERE CalendarDate BETWEEN  StartDate AND  EndDate       AND IsWorkDay   1    If you don t have that  you can use a numbers table       DECLARE       StartDate datetime    2014-01-01        EndDate datetime    2014-01-31        SELECT      SUM CASE WHEN DATEPART dw  DATEADD dd  Number-1   StartDate   BETWEEN 2 AND 6 THEN 1 ELSE 0 END  As NumberOfWeekDays     FROM dbo Numbers     WHERE Number  lt   DATEDIFF dd   StartDate   EndDate    1 -- Number table starts at 1  we want a 0 base   They should both be fast and it takes out the ambiguity complexity  The first option is the best but if you don t have a calendar table you can allways create a numbers table with a CTE

User · Answer

I borrowed some ideas from others to create my solution   I use inline code to ignore weekends and U S  federal holidays   In my environment  EndDate may be null  but it will never precede StartDate   CREATE FUNCTION dbo ufn CalculateBusinessDays   StartDate DATE   EndDate DATE   NULL   RETURNS INT AS  BEGIN DECLARE  TotalBusinessDays INT   0  DECLARE  TestDate DATE    StartDate    IF  EndDate IS NULL     RETURN NULL   WHILE  TestDate  lt   EndDate BEGIN     DECLARE  Month INT   DATEPART MM   TestDate       DECLARE  Day INT   DATEPART DD   TestDate       DECLARE  DayOfWeek INT   DATEPART WEEKDAY   TestDate  - 1  --Monday   1  Tuesday   2  etc      DECLARE  DayOccurrence INT     Day - 1    7   1  --Nth day of month  3rd Monday  for example       --Increment business day counter if not a weekend or holiday     SELECT  TotalBusinessDays              SELECT CASE             --Saturday OR Sunday             WHEN  DayOfWeek IN  6 7  THEN 0             --New Year s Day             WHEN  Month   1 AND  Day   1 THEN 0             --MLK Jr  Day             WHEN  Month   1 AND  DayOfWeek   1 AND  DayOccurrence   3 THEN 0             --G  Washington s Birthday             WHEN  Month   2 AND  DayOfWeek   1 AND  DayOccurrence   3 THEN 0             --Memorial Day             WHEN  Month   5 AND  DayOfWeek   1 AND  Day BETWEEN 25 AND 31 THEN 0             --Independence Day             WHEN  Month   7 AND  Day   4 THEN 0             --Labor Day             WHEN  Month   9 AND  DayOfWeek   1 AND  DayOccurrence   1 THEN 0             --Columbus Day             WHEN  Month   10 AND  DayOfWeek   1 AND  DayOccurrence   2 THEN 0             --Veterans Day             WHEN  Month   11 AND  Day   11 THEN 0             --Thanksgiving             WHEN  Month   11 AND  DayOfWeek   4 AND  DayOccurrence   4 THEN 0             --Christmas             WHEN  Month   12 AND  Day   25 THEN 0             ELSE 1             END AS Result        SET  TestDate   DATEADD dd  1   TestDate   END  RETURN  TotalBusinessDays  END

[sql] Count work days between two dates

Examples related to sql

Examples related to tsql

Examples related to date