Skip to content
Advertisement

How to get 1st and 3rd Saturday and all Sunday between 2 dates using sql

Given a date range, I’d like to return all of the Saturdays and Sundays that fall within that range, with these conditions:

  • Include Saturdays only if their ordinal position is either the 1st or 3rd Saturday within the month they fall (not within the entire range).
  • Include all Sundays, along with the ordinal position of that Sunday within the month it falls.

So for example, if the start date is Aug 15, 2021 and the end date is Sep 20, 2021, the output would be:

Then I can take the date range in total (37 days), and subtract the Sundays (6), and the 1st and 3rd Saturdays from each month (3), to end at 28.

Tried this query

it does not work properly.

enter image description here

Also Tried this solution Get number of weekends between two dates in SQL for calculate week days, but I want only 1st and 3 Saturday and all Sundays

Advertisement

Answer

Get a calendar table; it makes this type of business problem a breeze. Here’s a simpler one:

Now your query is easy:

Results (db<>fiddle example here):

And to get just the number 28:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement