# Count the Number of Weekend Days between Two Dates

• Jeff Moden - Thursday, August 23, 2018 10:47 PM

Sorry, Adam... I do honestly appreciate anyone that will step up to bat with an article and share the knowledge they have and I thank you for that but, considering the outcome of this discussion and the review of your code for this article (that you claim is part of your book?), you and your partner might want to consider rewriting certain sections of that book.

+(Number of pages in the book)
😎

• I agree with Scott, no need for anything but simple math.
😎
It is very disappointing when articles with sub-optimal solutions are published on this site but I'm pleased to see responses like this thread when that happens😉

Here is a function which is a modification of a function that calculates week days, which I've used for a long time. It does in fact use the same logic as Scott's code, but I must admit that Scott's code is more human readable 🙂
`USE TEEST;GOSET NOCOUNT ON;GOCREATE OR ALTER FUNCTION dbo.ITVFN_CALC_WEEKEND_DAYS----------------------------------------------------------------------- Calculate the number of weekend days between and including two-- dates.-- NOTE: This is a modification of the dbo.ITVFN_CALC_WEEK_DAYS--   algorithm.----------------------------------------------------------------------- USAGE: -- DECLARE @FROM_DATE DATE = '1900-01-01';-- DECLARE @TO_DATE  DATE = '2000-01-01';-- SELECT--  WED.FROM_DATE--  ,WED.TO_DATE--  ,WED.WEEK_END_DAYS-- FROM dbo.ITVFN_CALC_WEEKEND_DAYS(@FROM_DATE,@TO_DATE) WED---------------------------------------------------------------------(  @FROM_DATE DATE ,@TO_DATE  DATE)RETURNS TABLEWITH SCHEMABINDINGASRETURNWITH BASE_CALC AS(  SELECT   @FROM_DATE AS FD   ,@TO_DATE AS TD   ,DATEDIFF(DAY,@FROM_DATE,@TO_DATE) AS FDD   ,(DATEDIFF(DAY,0,@FROM_DATE) % 7)  AS SWD)SELECT  BC.FD AS FROM_DATE ,BC.TD AS TO_DATE ,(((1 + BC.FDD) / 7) * 2)   + SIGN(((BC.SWD) + ((1 + BC.FDD) % 7)) - 5)   + (SIGN(((BC.FDD) % 7)) * (((BC.SWD) + ((BC.FDD) % 7)) / 6)) AS WEEK_END_DAYSFROM  BASE_CALC BC;`

And an example of the logic using Scott's sample data set

`USE TEEST;GOSET NOCOUNT ON;`

`;WITH SAMPLE_DATA(FD,TD) AS(  SELECT    CONVERT(DATE,X.FD,112) AS FD   ,CONVERT(DATE,X.TD,112) AS TD  FROM  ( VALUES    ('20180301', '20180430')   ,('20180301', '20180429')   ,('20180301', '20180428')   ,('20180301', '20180304')   ,('20180301', '20180303')   ,('20180226', '20180302')   ,('20180826', '20180902')    ,('20180824', '20180902')   ) X(FD,TD)) ,BASE_CALC AS(  SELECT   SD.FD   ,SD.TD   ,DATEDIFF(DAY,SD.FD,SD.TD) AS FDD   ,(DATEDIFF(DAY,0,SD.FD) % 7) AS SWD  FROM  SAMPLE_DATA  SD)SELECT  BC.FD AS FROM_DATE ,BC.TD AS TO_DATE ,(((1 + BC.FDD) / 7) * 2)   + SIGN(((BC.SWD) + ((1 + BC.FDD) % 7)) - 5)   + (SIGN(((BC.FDD) % 7)) * (((BC.SWD) + ((BC.FDD) % 7)) / 6)) AS WEEK_END_DAYSFROM  BASE_CALC BC;`

Output
`FROM_DATE TO_DATE  WEEK_END_DAYS---------- ---------- -------------2018-03-01 2018-04-30 182018-03-01 2018-04-29 182018-03-01 2018-04-28 172018-03-01 2018-03-04 22018-03-01 2018-03-03 12018-02-26 2018-03-02 02018-08-26 2018-09-02 32018-08-24 2018-09-02 4`

• For final prod code, I'd probably make a couple of other minor adjustments to make the code more inherently clear.  I'm a firm believer in self-documenting code, including clear variable names, whenever possible.  Although, if you get used to always doing calcs based off of SQL's base 0 date (19000101), you get very familiar with 0 being Monday, 1 = Tuesday, etc. 🙂.  [For the record, this code works correctly under any/all DATEFIRST settings.]

`SELECT from_date, to_date,    days_diff / 7 * 2 /* whole weeks days */ +     CASE /* remainder days, cannot be more than 2 */        WHEN days_diff % 7 = 0 THEN 0        WHEN from_day = Sunday THEN 1        WHEN from_day + days_diff % 7 - 1 >= Sunday THEN 2        WHEN from_day + days_diff % 7 - 1 >= Saturday THEN 1        ELSE 0     END AS total_weekend_daysFROM ( VALUES ('20180301', '20180430'), ('20180301', '20180429'), ('20180301', '20180428'),  ('20180301', '20180304'), ('20180301', '20180303'), ('20180226', '20180302'),   ('20180826', '20180902') , ('20180824', '20180902') ) AS dates (from_date, to_date)CROSS APPLY (  SELECT DATEDIFF(DAY, from_date, to_date) + 1 AS days_diff,       DATEDIFF(DAY, 0, from_date) % 7 AS from_day,       5 AS Saturday, 6 AS Sunday) AS ca1`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• ScottPletcher - Friday, August 24, 2018 8:23 AM

[For the record, this code works correctly under any/all DATEFIRST settings.]

So does the function I posted above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Using a CTE seems overly complex. This is conceptually the same. I also agree with other posters saying that a Tally table may be overkill. The tally table could obviously be derived from any table of a suitable size. I just used the first to hand

`DECLARE @StartDate datetime =  '20180301'DECLARE @EndDate datetime =  '20180430'SELECT COUNT(*) FROM (SELECT ROW_NUMBER() over (ORDER BY object_id)  AS ID  FROM sys.objects) AS TallyWHERE ID< DATEDIFF(DD,@StartDate,@EndDate) AND DATEPART(DW,DATEADD(DD, ID, @StartDate)) IN (1,7)`
• Here's my version, also no recursion, with added alias names for more clarity:

`SELECT     start_date, end_date,    full_weeks * 2 +    CASE WHEN remaining_days = 0 THEN 0         WHEN start_day_of_week = Sun THEN 1         WHEN remaining_days + start_day_of_week >= Sun THEN 2         WHEN remaining_days + start_day_of_week = Sat THEN 1         ELSE 0 END AS weekend_day_countFROM (VALUES    (CAST('20180301' AS date), CAST('20180430' AS date)),        ('20201004','20201009'),    ('20201004','20201010'),    ('20201004','20201011'),    ('20201004','20201012'),    ('20201004','20201013'),    ('20201004','20201014'),    ('20201004','20201015'),    ('20201004','20201016'),    ('20201004','20201017'),    ('20201004','20201018')    ) AS test_dates(start_date, end_date)CROSS APPLY (    SELECT DATEDIFF(DAY, start_date, end_date) + 1 AS total_days,        DATEDIFF(DAY, 0, start_date) % 7 AS start_day_of_week,        0 AS Mon, 5 AS Sat, 6 AS Sun) AS calc1CROSS APPLY (    SELECT total_days / 7 AS full_weeks,        total_days % 7 AS remaining_days        ) AS calc2`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• ScottPletcher wrote:

Here's my version, also no recursion, with added alias names for more clarity:

`SELECT     start_date, end_date,    full_weeks * 2 +    CASE WHEN remaining_days = 0 THEN 0         WHEN start_day_of_week = Sun THEN 1         WHEN remaining_days + start_day_of_week >= Sun THEN 2         WHEN remaining_days + start_day_of_week = Sat THEN 1         ELSE 0 END AS weekend_day_countFROM (VALUES    (CAST('20180301' AS date), CAST('20180430' AS date)),        ('20201004','20201009'),    ('20201004','20201010'),    ('20201004','20201011'),    ('20201004','20201012'),    ('20201004','20201013'),    ('20201004','20201014'),    ('20201004','20201015'),    ('20201004','20201016'),    ('20201004','20201017'),    ('20201004','20201018')    ) AS test_dates(start_date, end_date)CROSS APPLY (    SELECT DATEDIFF(DAY, start_date, end_date) + 1 AS total_days,        DATEDIFF(DAY, 0, start_date) % 7 AS start_day_of_week,        0 AS Mon, 5 AS Sat, 6 AS Sun) AS calc1CROSS APPLY (    SELECT total_days / 7 AS full_weeks,        total_days % 7 AS remaining_days        ) AS calc2`

There seems to be an issue when the week starts on Monday.

When using ( '20200601', '20200605' ) -- Mon - Fri, the weekend_day_count comes up as 1, when it should be 2

When using ( '20200601', '20200606' ) -- Mon - Sat, the weekend_day_count comes up as 2, when it should be 1

• This is a simple math problem, that does not require CTEs , recursion or iteration.

`DECLARE @from_date DATE = '1-Oct-2020';DECLARE @to_date DATE = '25-Oct-2020';select @from_date as From_Date, @to_date as To_Date, DATEADD(d, -DATEPART(dw, @from_date), @from_date ) as From_Prev_Sunday, DATEADD(d, -DATEPART(dw, @to_date), @to_date ) as To_Prev_Sunday, DATEDIFF(week , DATEADD(d, -DATEPART(dw, @from_date), @from_date ), DATEADD(d, -DATEPART(dw, @to_date), @to_date ) ) as Num_Sundays`

GrayB

• You should count both saturdays and sundays.

Buth with your simple math solution, where you only counts sundays, the results is wrong. 2 exampels:

DECLARE @from_date DATE = '11-Oct-2020'; -- sunday

DECLARE @to_date DATE = '18-Oct-2020'; -- sunday

The result is 1.

And if we try with

DECLARE @from_date DATE = '11-Oct-2020'; -- sunday

DECLARE @to_date DATE = '16-Oct-2020'; -- frieday

If "SET DATEFIRST 1" the result is 1

If "SET DATEFIRST 7" the result is 0

So maybe not just a simple solution!

• It's late in the day, and I'm below my quota of coffee (all too common these days, I really need to rekindle my coffee habit), but here's another, fairly concise approach that should work:

`DECLARE @test_dates TABLE (start_date DATE, end_date DATE);INSERT INTO @test_dates (start_date,end_date) VALUES ('20180301','20180430'),    ('20201004','20201009'),('20201004','20201010'),('20201004','20201011'),('20201004','20201012'),('20201004','20201013'),('20180301','20180429'),('20180301','20180428'),('20201004','20201014'),('20201004','20201015'),('20201004','20201016'),('20200601','20200605'),('20200601','20200606'),('20201004','20201017'),('20201004','20201018');SELECT start_date,       end_date,       weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7 FROM   @test_dates           CROSS APPLY        (VALUES(DATEDIFF(DAY,6,start_date),DATEDIFF(DAY,6,end_date)))x(x,y);`

Cheers!

• Jacob Wilkins wrote:

It's late in the day, and I'm below my quota of coffee (all too common these days, I really need to rekindle my coffee habit), but here's another, fairly concise approach that should work:

`DECLARE @test_dates TABLE (start_date DATE, end_date DATE);INSERT INTO @test_dates (start_date,end_date) VALUES ('20180301','20180430'),    ('20201004','20201009'),('20201004','20201010'),('20201004','20201011'),('20201004','20201012'),('20201004','20201013'),('20180301','20180429'),('20180301','20180428'),('20201004','20201014'),('20201004','20201015'),('20201004','20201016'),('20200601','20200605'),('20200601','20200606'),('20201004','20201017'),('20201004','20201018');SELECT start_date,       end_date,       weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7 FROM   @test_dates           CROSS APPLY        (VALUES(DATEDIFF(DAY,6,start_date),DATEDIFF(DAY,6,end_date)))x(x,y);`

Cheers!

That's very clever (especially since your low caffeine alarm is ringing :D) and, because of the integer math, is likely to be very fast.  Unfortunately, I believe there's a fly in the ointment.  Consider the following code that determines the day of the week for the given start and end dates to be used in an upcoming test...

` SELECT  DOW_19000101 = DATENAME(dw,'19000101')        ,DOW_20991231 = DATENAME(dw,'20991231');`

That returns the following...

The significance there is that, because the start date is a Monday and the end date is a Thursday, there MUST be an even number of days returned because neither end of the date range has split a weekend.

If we run your good code using those two dates as follows (please correct me if I've somehow used it incorrectly)...

` SELECT weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7    FROM (VALUES(DATEDIFF(DAY,6,'19000101'),DATEDIFF(DAY,6,'20991231')))x(x,y);`

... it results in the following, which is an odd number... which means there's a flaw somewhere in the formula or, perhaps, it simply cannot handle dates prior to 1900-01-07 (which is what your use of the number "6" indicates)... which means we can't use this formula until there's a bit of proof as to what the problem is.  If it's the latter, I'm ok with saying that the start date must be after 1900-01-07 for use but we have to prove that's just a range limitation and not an actual problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• He he, look what the cat dragged in!

😎

More than two years on and it's still interesting? Feels like eating last week's take out pizza.

• Eirikur Eiriksson wrote:

He he, look what the cat dragged in!

😎

More than two years on and it's still interesting? Feels like eating last week's take out pizza.

Heh... I've done that.  It does take proper storage and preparation to prevent the screaming meemies though. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• We can make a simple change to Jacob's solution to fix the problem:

`DECLARE @test_dates TABLE (start_date DATE, end_date DATE);INSERT INTO @test_dates (start_date,end_date) VALUES ('20180301','20180430'),    ('20201004','20201009'),('20201004','20201010'),('20201004','20201011'),('20201004','20201012'),('20201004','20201013'),('20180301','20180429'),('20180301','20180428'),('20201004','20201014'),('20201004','20201015'),('20201004','20201016'),('20200601','20200605'),('20200601','20200606'),('20201004','20201017'),('20201004','20201018'),('19000101','20991231');SELECT start_date,       end_date,       weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7 FROM   @test_dates           CROSS APPLY        (VALUES(DATEDIFF(DAY,-1,start_date),DATEDIFF(DAY,-1,end_date)))x(x,y);`

However, this solution will not work for dates prior to 1900-01-01 unless we force the DATEDIFF calculation to use the DATE data type.  Using an integer value as the first parameter forces DATEDIFF to implicitly convert the values to DATETIME and the negative value will be 1 off the total.

`DECLARE @test_dates TABLE (start_date DATE, end_date DATE);INSERT INTO @test_dates (start_date,end_date) VALUES ('19000101','20991231'),('17530101','20991231');SELECT start_date,       end_date,       weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7 FROM   @test_dates           CROSS APPLY        (VALUES(DATEDIFF(DAY,-1,start_date),DATEDIFF(DAY,-1,end_date)))x(x,y);SELECT start_date,       end_date,       weekend_day_count=y/7-x/7+(y+1)/7-(x-1)/7 FROM   @test_dates           CROSS APPLY        (VALUES(DATEDIFF(DAY,cast('1752-12-31' As date),start_date),DATEDIFF(DAY,cast('1752-12-31' As date),end_date)))x(x,y);`

This also leads to the problem of the 'first' week of the DATE data type - 0001-01-01 through 0001-01-06 cannot be used because we cannot define the Sunday prior to 0001-01-01 as a date.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• Jeff,

Thanks for taking a look.

The problem is indeed one of the range (as Jeffrey also pointed out while I typed this up). The math trick won't work once negative numbers get involved.

If we push back the anchor Sunday to before the interval you specify for that test, then it returns 20870, as expected, although this can't always be done. I'm not sure I get the business use of figuring out weekends at the beginning of allowed date or datetime data ranges (like the year 0), but it's a fair technical concern for thoroughness' sake, of course :).

I'm normally the paranoid sort that would point out those sorts of caveats when posting, but my diligence was flagging when I posted that; thanks for pointing it out!

Could be addressed in a number of ways, ranging from just putting a caveat on its use to having the query automatically adjust negative numbers appropriately.

I've been rolling this problem around in my head the last couple days, trying to see if I can come up with something even more concise, so I'll post some alternatives once I've given up on the brevity game.

Cheers!

Viewing 15 posts - 31 through 45 (of 63 total)