Snowflake DateDiff Business Days: How do I exclude weekends?
Posted: July 24, 2023
This is the code leverages Snowflake DateDiff to calculate business days.
select
DATEDIFF('day', '2022-07-01', '2022-07-14') + 1
- DATEDIFF('week', '2022-07-01', DATEADD('day', 1, '2022-07-14'))
- DATEDIFF('week', '2022-07-01', '2022-07-14') as working_days
;
It looks a little wonky. I'll break it down into steps. Basically we are calculating three values: a, b, & c. Then we are subtracting b & c from a: a-b-c.
DateDiff Naive Count of Days
This first expression naively counts days between start and end. The +1 means the end date is included as part of the count.
DATEDIFF('day', '2022-07-01', '2022-07-14') + 1
Subtract Saturdays
This second expression counts Saturdays. There can never be fewer Saturdays than Sundays in a given range because Snowflake starts the week on Monday and Sunday is after Saturday. We want to check whether the last day in the range is a Saturday. This expression checks by calculating how many weeks there are in this range if the last day is forwarded by one day.
For example, if the last day is a Friday, then it moves to Saturday, and that partial week doesn't count towards our total because DATEDIFF('week', ...) only counts fully completed weeks.
However, if the last day is a Saturday, then it gets bumped to a Sunday, and this count will increase.
- DATEDIFF('week', '2022-07-01', DATEADD('day', 1, '2022-07-14'))
Subtract Sundays
This third piece counts the number of fully completed weeks, which will be the same as the number of Sundays in that range (there is one Sunday per week).
- DATEDIFF('week', '2022-07-01', '2022-07-14')
Putting it all together with DateDiff to get Business Days
Putting all the expressions together, we subtract the Saturday count and Sunday count from the first naive count to calculate the number of weekdays elapsed in the time range.
select
DATEDIFF('day', '2022-07-01', '2022-07-14') + 1
- DATEDIFF('week', '2022-07-01', DATEADD('day', 1, '2022-07-14'))
- DATEDIFF('week', '2022-07-01', '2022-07-14') as working_days
;
This pattern can be adjusted to accommodate different week start days.
Plus 5 Business Days
As a follow up to the same idea I had to deploy logic that returned the date that is 5 business days from any given calendar date. It's pretty straightforward once you wrap your head around it. Including examples for plus 4 business days as well.
, case
when day_name = 'Sunday' then dateadd('day',4,date)
when day_name = 'Monday' then dateadd('day',4,date)
when day_name = 'Saturday' then dateadd('day',5,date)
else dateadd('day',6,date)
end as plus_4_business_days
, case
when day_name = 'Sunday' then dateadd('day',5,date)
when day_name = 'Saturday' then dateadd('day',6,date)
else dateadd('day',7,date)
end as plus_5_business_days