Calculating days to excluding weekends (Monday to Friday) in SQL Server

Question

How can I calculate the number of work days between two dates from table (from the 1st row to the end) in SQL Server 2008?

I tried something like this, but it does not work

DECLARE @StartDate as DATETIME, @EndDate as DATETIME

Select @StartDate = date2 from testtable ;
select @EndDate = date1 from testtable ;

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)

http://stackoverflow.com/questions/1803987/how-do-i-exclude-weekend-days-in-a-sql-server-query — arjen1984, Nov 04 '13 at 10:41
think thats not try, I have Date1 = 2013-09-24 19:26:39 and Date2 = 2013-09-26 16:29:31 where Date1 and Date2 dynamic date — user2943856, Nov 04 '13 at 11:00
We are using all dates containing table for such tasks. Genrally we link to this table like "datestable.date between startdate and enddate" and just count dates (records) from dates table, where conditions (like date is workday) are met. — Arvo, Nov 04 '13 at 11:35

GarethD · Accepted Answer · 2013-11-04T13:26:33.083

I would always recommend a Calendar table, then you can simply use:

SELECT  COUNT(*)
FROM    dbo.CalendarTable
WHERE   IsWorkingDay = 1
AND     [Date] > @StartDate
AND     [Date] <= @EndDate;

Since SQL has no knowledge of national holidays for example the number of weekdays between two dates does not always represent the number of working days. This is why a calendar table is a must for most databases. They do not take a lot of memory and simplify a lot of queries.

But if this is not an option then you can generate a table of dates relatively easily on the fly and use this

SET DATEFIRST 1;
DECLARE @StartDate DATETIME = '20131103', 
        @EndDate DATETIME = '20131104';

-- GENERATE A LIST OF ALL DATES BETWEEN THE START DATE AND THE END DATE
WITH AllDates AS
(   SELECT  TOP (DATEDIFF(DAY, @StartDate, @EndDate))
            D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @StartDate)
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b
)
SELECT  WeekDays = COUNT(*)
FROM    AllDates
WHERE   DATEPART(WEEKDAY, D) NOT IN (6, 7);

EDIT

If you need to calculate the difference between two date columns you can still use your calendar table as so:

SELECT  t.ID,
        t.Date1,
        t.Date2,
        WorkingDays = COUNT(c.DateKey)
FROM    TestTable t
        LEFT JOIN dbo.Calendar c
            ON c.DateKey >= t.Date1
            AND c.DateKey < t.Date2
            AND c.IsWorkingDay = 1
GROUP BY t.ID, t.Date1, t.Date2;

Example on SQL-Fiddle

please see http://img707.imageshack.us/img707/1346/luyk.jpg But string_val is not counting the days — user2943856, Nov 04 '13 at 12:56
String_Val is counting the days between `@d1` and `@d2`, but not between the dates in your table (date1 and date2), this is why you are getting the same number repeated in all rows. — GarethD, Nov 04 '13 at 13:26

score 2 · Answer 2 · answered Nov 04 '13 at 12:54

This does it excluding the days out but date part rather than description. You can substitute the parameters used as an example for the values in your query.

Declare 
    @startdate datetime = '2013-11-01', 
    @enddate datetime = '2013-11-11'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(case datepart(dw, @StartDate)+@@datefirst when 8 then 1 else 0 end) 
  -(case datepart(dw, @EndDate)+@@datefirst when 7 then 1 when 14 then 1 else 0 end) 

Returns 7

score 1 · Answer 3 · answered Jul 26 '17 at 18:11

To add to GarethD's answer - I put together the SQL for a USA version of the Calendar table, with all holidays and weekends set to is_working_day = false... for anyone that would like the SQL, here it is:

declare @start_dt as date = '1/1/2009';     -- Date from which the calendar table will be created.
declare @end_dt as date = '1/1/2030';       -- Calendar table will be created up to this date (not including).

create table CalendarTable (
 date_id date primary key,
 date_year smallint,
 date_month tinyint,
 date_day tinyint,
 weekday_id tinyint,
 weekday_nm varchar(10),
 month_nm varchar(10),
 day_of_year smallint,
 quarter_id tinyint,
 first_day_of_month date,
 last_day_of_month date,
 start_dts datetime,
 end_dts datetime,
 week_number_of_month int,
 is_working_day bit,
)

while @start_dt < @end_dt
begin
    insert into CalendarTable(
        date_id, date_year, date_month, date_day, 
        weekday_id, weekday_nm, month_nm, day_of_year, quarter_id, 
        first_day_of_month, last_day_of_month, 
        start_dts, end_dts, week_number_of_month, is_working_day
    )   
    values(
        @start_dt, year(@start_dt), month(@start_dt), day(@start_dt), 
        datepart(weekday, @start_dt), datename(weekday, @start_dt),     datename(month, @start_dt), datepart(dayofyear, @start_dt), datepart(quarter,     @start_dt),
        dateadd(day,-(day(@start_dt)-1),@start_dt), dateadd(day,-(day(dateadd(month,1,@start_dt))),dateadd(month,1,@start_dt)), 
        cast(@start_dt as datetime), dateadd(second,-1,cast(dateadd(day, 1, @start_dt) as datetime)), DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_dt), 0), @start_dt) +1, 0
    )
    set @start_dt = dateadd(day, 1, @start_dt)
end
GO

-- Set all non-weekend days as business days
update CalendarTable set is_working_day = 1 where weekday_id not in (1,7)
GO

-- New Years Day
update CalendarTable set is_working_day = 0 where date_month = 1 and date_day = 1
GO

-- Memorial Day (last Monday of May)
WITH Memorial_Day AS
(
    SELECT date_id, date_year, date_day,
    ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day desc) AS RowNumber
    FROM CalendarTable
    where date_month = 5 and weekday_id = 2
)   

update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Memorial_Day 
                                                            where rownumber = 1)
GO

-- Independence Day
update CalendarTable set is_working_day = 0 where date_month = 7 and date_day = 4
GO


-- Labor Day (first Monday in September)
WITH Labor_Day AS
(
    SELECT date_id, date_year, date_day,
    ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
    FROM CalendarTable
    where date_month = 9 and weekday_id = 2
)   

update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Labor_Day 
                                                            where rownumber = 1)
GO

-- Thanksgiving (fourth Thursday in November)
WITH Thanksgiving AS
(
    SELECT date_id, date_year, date_day,
    ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
    FROM CalendarTable
    where date_month = 11 and weekday_id = 5
)   

update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Thanksgiving 
                                                            where rownumber = 4)

GO

-- Day After Thanksgiving (fourth Friday in November)
WITH DayAfterThanksgiving AS
(
    SELECT date_id, date_year, date_day,
    ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
    FROM CalendarTable
    where date_month = 11 and weekday_id = 6
)   

update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM DayAfterThanksgiving 
                                                            where rownumber = 4)

GO

-- Christmas Day
update CalendarTable set is_working_day = 0 where date_month = 12 and date_day = 25
GO

score 0 · Answer 4 · answered Jul 19 '18 at 11:35

You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. You also can calculate holidays between start/end date and can subtract that from final selection.

Declare @startdate as DateTime
Declare @enddate as DateTime
Set @startdate = GETDATE()-2;
set @enddate = GETDATE()+3;
select @startdate,@enddate,(datediff(day,@startdate,@enddate+1)-(2)*datediff(week,@startdate,@enddate))

score 0 · Answer 5 · answered Sep 01 '18 at 17:17

Interesting question. It is always important to understand the use case. If counting from Sunday to Monday, would we want to say there is one day as if we had until close of business on Monday. Or would we want to say there are no days as if there were no days before Monday began. In our case we needed to count both days (start and end) if they were weekdays, because I was working in a payroll application estimating some accruals. And any day weekend days would later be accounted for by holiday, and overtime records.

When I pulled out my calendar I realized that when counting from Saturday or Sunday we could just start counting from Monday. And also when counting to a Saturday or Sunday I could just stop counting when we got to Friday. So I wrote a function that adjusted the starting and ending dates, Found the number of weeks by dividing by 7, multiplied by that 5 weekdays per week and then added back the remainder. I did have to account for the case when we started counting on a weekend but never got to a Monday.

-- ============================================= -- Author: Todd P Payne -- Create date: 9/1/2018 -- Description: Counts number of weekdays between two dates -- Unlike DateDiff StartDate and EndDate are inclusive -- FROM Monday, Jan 1 to Monday Jan 1 will return 1 -- ============================================= CREATE FUNCTION [dbo].[ufnCountWeekdays] ( -- Add the parameters for the function here @StartDate DateTime, @EndDate DateTime ) RETURNS INT AS BEGIN -- Declare the return variable here DECLARE @CountofWeekDays INT = NULL; DECLARE @TempDate DateTime;

-- Could CountBackwords 
IF @StartDate > @ENDDate
BEGIN
    SET @TempDate = @StartDate; 
    SET @StartDate = @EndDate;
    SET @EndDate = @TempDate;
END

--Start on Weekend Never get to Monday Case
IF (DatePart(dw,@StartDate) = 7 AND DateDiff(Day,@StartDate,@EndDate) < 2)
OR (DatePart(dw,@StartDate) = 1 AND DateDiff(Day,@StartDate,@EndDate) < 1 )
BEGIN 
    SET @CountOfWeekDays = 0 -- Never got to a WeekDay
END

--NORMAL CASE
ELSE BEGIN 
 -- IF Sat Start just pretend Start Counting on Monday 
 IF (DatePart(dw,@StartDate) = 7) SET @StartDate = dateadd(Day, 2, @StartDate);
 -- IF Sun Start just Start to Counting on Monday 
 IF (DatePart(dw,@StartDate) = 1) SET @StartDate = dateadd(Day, 1, @StartDate);
 -- Sat End just Stop counting on Friday
 IF (DatePart(dw,@EndDate) = 7) SET @EndDate = DateAdd(Day, -1, @EndDate);
 -- Sun END
 if (DatePart(dw,@EndDate) = 1) SET  @EndDate = DateAdd(Day, -2, @EndDate);
 --Find the total number of days we need to count
 Declare @DaysToCount INT = DateDiff(Day,@StartDate, @EndDate)+1; --include start
 -- five days for each full week plus any other weekdays 
 -- remember no worries about starting or ending on weekends
  Set @CountofWeekDays  = Floor(@DaysToCount/7)*5 + (@DaysToCount % 7)

END     

--Check to see if we are counting backwards
IF @TempDate = @EndDate SET @CountofWeekDays = -1 * @CountofWeekDays;

RETURN @CountofWeekDays;

END GO

Happy Coding

Todd P Payne

Sorry first post ever and I some how managed to get the code snippet in twice. — TPayne, Sep 01 '18 at 17:19

score 0 · Answer 6 · edited Oct 31 '22 at 02:59

I have improved upon @GarethD answer to cater for both any server localisations that may be present, but also to allow the excluded days to be a variable in a comma delimited string.

This is also fully inclusive of the first date in the range.

DECLARE @Start DATETIME = '10/01/2022';
DECLARE @End DATETIME = '10/31/2022';
DECALRE @ExcludedDays NVARCHAR(max) = '0,1';

--Comma Delimited String where 0=Saturday to 6=Friday.
--Any number above 6 will include all dates.


-- Generate a table of all available dates.
    WITH AllDates AS
    (   SELECT  TOP (DATEDIFF(DAY, @Start, @End)+1) --+1 to be inclusive of the first date
                D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), dateadd(day,-1,@Start))  --Use the day before to be inclusive of the first date
        FROM    sys.all_objects a
                CROSS JOIN sys.all_objects b
    )
    --Now just select the days of week we want
    SELECT  TotalDays = COUNT(*)
    FROM    AllDates
    WHERE   ((DATEPART(dw, D) + @@DATEFIRST) % 7) NOT IN (select value from dbo.fn_Split(@ExcludedDays,',')); -- Exclude required days from report

To split the @ExcludedDays variable we use a table-value function to split the string into returnable values [fn_Split].
(SQL server 2016 > can use the function STRING_SPLIT)

Calculating days to excluding weekends (Monday to Friday) in SQL Server

6 Answers6

Linked

Related