SQL Server : how do you exclude weekends using datediff?

Question

I am still learning SQL so please bare that in mind. I have a query that returns me the average days for a specific range, although my range does not account for weekends & holidays. Holidays may be a little tricky but how do I exclude weekends from my range?

For example I have a range 02-01-18 to 02-15-18 where the datediff is 14 days, but how do I get SQL to identifying which days in that range were weekends and if they were to exclude them from my datediff?

My query is

SELECT
    AVG(1.00 * DATEDIFF(DAY, xx, yy)) AS DayDiff
FROM 
    datebase1.dbo.table1
WHERE
    MONTH(datecompleted) = MONTH(DATEADD(month, -1, current_timestamp))
    AND YEAR(datecompleted) = YEAR(DATEADD(month, -1, current_timestamp))
    AND ApprovalRequiredFrom = 'pp'

I do have a calendar I can source which tells me the date and the name of the day, but I want to avoid having to do this. I want to be able to exclude the weekends from my range to get me a more accurate result.

Thanks

This been asked before: https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server — Arta S, Mar 26 '18 at 18:08
Possible duplicate of [How do I exclude Weekend days in a SQL Server query?](https://stackoverflow.com/questions/1803987/how-do-i-exclude-weekend-days-in-a-sql-server-query) — Kevin, Mar 26 '18 at 18:18
I have read both of those questions, I need some help with the correct syntax for WHERE ((DATEPART(dw, xx, yy) + @@DATEFIRST) % 7) NOT IN (0, 1)? — Pat, Mar 26 '18 at 18:36
I do this with a DimDate dimension and use that for your date work. If you had daily granularity then it would be (Select Count(*) from DimDate where DateVal between xx and yy and DayOfWeek not in (1,7) — KeithL, Mar 26 '18 at 18:50
https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql not sure why you think DatePart has 3 arguments. — Tab Alleman, Mar 26 '18 at 19:52

score 1 · Answer 1 · answered Mar 27 '18 at 07:39

To exclude weekends, you need to filter Saturdays and Sundays from both your comparing dates (xx and yy). To do so you use the DATEPART function with the WEEKDAY parameter. The result is a number from 1 to 7 indicating which day of the week it is.

-- 2018-01-01 is a monday
SELECT DATEPART(WEEKDAY, '2018-01-01')
-- Result: 2

The problem is that different database configurations have the date related to the value "1" changed, so maybe the day of week "1" means Sunday for some and Mondays for others. To unify this, you can change the default with SET DATEFIRST.

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

-- 2018-01-01 is a monday
SELECT DATEPART(WEEKDAY, '2018-01-01')
-- Result: 1

Another solution that is datefirst agnostic is to use the @@DATEFIRST session value directly on your expression. The @@DATEFIRST holds the value we set on the SET DATEFIRST statement, or the database default. Please notice that the result is the same, even changing DATEFIRST.

SET DATEFIRST 7 -- 1: Sunday, 2: Monday

-- 2018-01-01 is a monday
SELECT (DATEPART(WEEKDAY, '2018-01-01') + @@DATEFIRST) % 7
-- Result: 2


SET DATEFIRST 1 -- 1: Monday, 7: Sunday

-- 2018-01-01 is a monday
SELECT (DATEPART(WEEKDAY, '2018-01-01') + @@DATEFIRST) % 7
-- Result: 2

For your example, you need to filter xx and yy dates to not be weekends. Add the following to your WHERE clause:

WHERE
    --...
    AND (DATEPART(WEEKDAY, xx) + @@DATEFIRST) % 7 NOT IN (0, 1)
    AND (DATEPART(WEEKDAY, yy) + @@DATEFIRST) % 7 NOT IN (0, 1)

score 0 · Answer 2 · answered Jan 23 '19 at 05:05

Because need to work with the old MySQL 5.1 server, just got a chance to try a "math" way to calculate no. of SAT / SUN to subtract from:

Note: MySQL's "weekday" function returns 0 for Mon, 5 for SAT and 6 for SUN, thus you see below SQL has some magic no. with 5 and 6.

Sample:

select  floor((datediff (ed, st)+1) / 7)*2 /*complete week's weekends*/
        + case when floor((datediff (ed, st) +1) % 7) between 0 and 6 /*additional weekends besides complete weeks*/
                then case when weekday(ed) >= weekday(st) then least(floor((datediff (ed, st) +1) % 7), greatest(least(6, weekday(ed)) - greatest(5, weekday(st)) + 1,0))
                                else least(floor((datediff (ed, st) +1) % 7), greatest(least(6, weekday(ed)+7) - greatest(5, weekday(st)) + 1,0)) end
            else 0
        end as num_of_sat_and_sun
from (select '2019-01-07' as st, '2019-01-12' as ed) x

SQL Server : how do you exclude weekends using datediff?

2 Answers2