Snowflake SQL: How do I exclude weekends between two dates in Snowflake SQL?

This post has been updated with additional content and a cleaner format. You can read the updated 2023 version here:

Snowflake DateDiff Business Days: How Do I Exclude Weekends?

Original Post

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
;

DATEDIFF('day', '2022-07-01', '2022-07-14') + 1 select DATEDIFF('day', '2022-07-01', '2022-07-14') + 1

This first expression naively counts days between start and end. The +1 means the end_date is included as part of the count.

- DATEDIFF('week', '2022-07-01', DATEADD('day', 1, '2022-07-14'))

This second expression counts Saturdays. Since 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 will 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', '2022-07-14') as working_days
;

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

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.

This pattern can be adjusted to accommodate different week start days

Resources:

Snowflake DateDiff Business Days: How Do I Exclude Weekends?
https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-calendar-weeks-weekdays