SOQL to get all records with in a given date range

Question

Below is my sample data:

I am trying to form a query where I can get records within a date range.

Date Range is start of the week to end of the week. For example I want all records that falls between July 10th - July 16 AND July 24 to July 30.

What I tried is having all the startweek in a Set<Date> setOfStartWeek and endWeek in Set<Date> setOfendWeek. Used below query :

List<Record> recList = [Select Id,Name,StartDate from Record where StartDate >= :setOfStartWeek AND StartDate <= :setOfEndWeek];

Desired Result => return records between July 10th - July 16 i.e records 1,2 AND return records between July 24 to July 30 i.e record 5,6.

The volume of the records is low = 3600 The date range can be dynamic, and not in my control. I can get setOfStartWeekDates that has July 10,24,31.. max it could contain 12 weeks from first element i.e. July 10 - Sept 25.

An approach but not sure if it would work

I will have a Set setOfdates = {July 10,24,31}. A formula field on the record that give me the start week date like July 10, July 24. Can I use in my SQOL- Where startWeekDate In: setOfStartWeeks , however I this doesn't seem to be working for me. Can we not use a set for Date field in where clause ? like how we do it : Id in :setOfIds ?

Answer 1

Logic to determine if records are within multiple non overlapping date ranges can only be done via post processing. It isn't supported in SOQL at arbitrary scale. If you have only two ranges you are looking at, you could use OR syntax. It won't scale up very far though.

Answer 2

I ended up using the formula field to identify the start of the week and used set of dates in where clause like how i described in comments