Calculating Days to Excluding Weekends (Monday to Friday) in SQL Server

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

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

get DATEDIFF excluding weekends using sql server

Example query below, here are some details on how I solved it.

Using DATEDIFF(WK, ...) will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how many weekends passed between the dates.

So we can multiple that value by 2 to get the number of weekend days that occurred and subtract that from the DATEDIFF(dd, ...) to get the number of weekdays.

This doesn't behave 100% correctly when the start or end date falls on Sunday, though. So I added in some case logic at the end of the calculation to handle those instances.

You may also want to consider whether or not the DATEDIFF should be fully inclusive. e.g. Is the difference between 9/10 and 9/11 1 day or 2 days? If the latter, you'll want to add 1 to the final product.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -
       case when datepart(dw, @d1) = 1 then 1 else 0 end +
      case when datepart(dw, @d2) = 1 then 1 else 0 end

How do I exclude Weekend days in a SQL Server query?

When dealing with day-of-week calculations, it's important to take account of the current DATEFIRST settings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.

SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)

Datediff excluding weekends

This method is checking if TAT is negative, and then returning 0 instead if that is the case:

SELECT STEP_ONE, STEP_TWO,

IIF (
        (DATEDIFF(dd, STEP_ONE,STEP_TWO))
        -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
        -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
        -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
        <= 0,
        0,
        (DATEDIFF(dd, STEP_ONE,STEP_TWO))
        -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
        -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
        -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
    ) AS TAT 

FROM TEST_1

The two long expressions of 4 lines each are identical to the one you wrote.

EDIT:
To make the condition only affect TATs that were computed on weekends (as you asked for in your comment below) you can wrap the IIF inside another IIF that asks about weekends. Here is the resulting even longer query:

SELECT STEP_ONE, STEP_TWO,

IIF (@@DATEFIRST = 1 AND (DatePart(dw, STEP_ONE) > 5 OR DatePart(dw, STEP_TWO) > 5)
  OR @@DATEFIRST = 7 AND (DatePart(dw, STEP_ONE) IN (1, 7) OR DatePart(dw, STEP_TWO) IN (1, 7)),

    IIF (
            (DATEDIFF(dd, STEP_ONE,STEP_TWO))
            -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
            -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
            -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
            <= 0,
            0,
            (DATEDIFF(dd, STEP_ONE,STEP_TWO))
            -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
            -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
            -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end
        )),
    (DATEDIFF(dd, STEP_ONE,STEP_TWO))
    -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
    -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
    -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
) AS TAT 

FROM TEST_1

SQL Query to find out the non weekends (Monday-Friday) dates of a week from a variable date

I am getting the required output using below SQL query :-

SELECT DATEADD(week,DATEDIFF(week,0,GETDATE()),0)
SELECT DATEADD(week,DATEDIFF(week,0,GETDATE()),4)

sql server get last previous date excluding weekend

You could check for today being 'Monday' and subtract 3 days, otherwise subtract 1:

SELECT DATEADD(
    day,
    IIF(DATENAME(weekday, GETDATE()) = 'Monday', -3, -1),
    CAST(GETDATE() AS DATE)
)

Count work days between two dates

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...

How can I exclude weekends and holidays in SQL Server query

I suggest to create a function that recursively verify the next working day based on the table that contains weekends and holidays. The advantage of this approach is that it is a reusable function whenever you need it.

This function receives the date and time. (Based on the code in your question) if the time is after 5pm, adds a day. After, continues checking if the date is not within weekends or holidays until find the next working day:

CREATE FUNCTION dbo.adjustedDate(@dateReceived DATETIME, @timeReceived TIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @adjustedDate DATETIME = @dateReceived

    -- Verify time to add 1 day to @adjustedDate
    IF @timeReceived IS NOT NULL
        IF @timeReceived > CONVERT(TIME, '5:00:00 PM')
             SET @adjustedDate = DATEADD(DAY, 1, @adjustedDate)

    -- Continue adding 1 day to @adjustedDate recursively until find one date that is not a weekend or holiday
    IF EXISTS(SELECT [Weekends & Holidays]
                FROM dbo.WeekendsHoliday
                WHERE [Weekends & Holidays] = @adjustedDate)                
        SET @adjustedDate = dbo.adjustedDate(DATEADD(DAY, 1, @adjustedDate), NULL)

    RETURN @adjustedDate
END

---

Related Topics