Check if date is between date range

Question

I am trying to check if a persons birthdate is between the next 8 and 14 days.

As an example: Today is the 10th of February - Check if the persons birthdate >= than 18/02 and <= 24/02.

Today is the 21th of February - Check if the persons birthdate >= 29/02 and <= 06/03.

This WHERE part will work for example 1:

(datepart(day, PersonBirthdate) >= datepart(day, DATEADD(day, 8, GetDate()) AT TIME ZONE 'Central European Standard Time')
AND datepart(month, PersonBirthdate) = datepart(month, DATEADD(day, 8, GetDate()) AT TIME ZONE 'Central European Standard Time'))
AND (datepart(day, PersonBirthdate) <= datepart(day, DATEADD(day, 14, GetDate()) AT TIME ZONE 'Central European Standard Time')
AND datepart(month, PersonBirthdate) = datepart(month, DATEADD(day, 14, GetDate()) AT TIME ZONE 'Central European Standard Time'))

So for the first example this expression is valid: 18 <= 20 <= 24.

But this one isn't for example 2: 29 <= 29 <= 3.

So this WHERE clause will work for example 2:

(datepart(day, PersonBirthdate) >= datepart(day, DATEADD(day, 8, GetDate()) AT TIME ZONE 'Central European Standard Time')
    AND datepart(month, PersonBirthdate) = datepart(month, DATEADD(day, 8, GetDate()) AT TIME ZONE 'Central European Standard Time'))
    OR (datepart(day, PersonBirthdate) <= datepart(day, DATEADD(day, 14, GetDate()) AT TIME ZONE 'Central European Standard Time')
    AND datepart(month, PersonBirthdate) = datepart(month, DATEADD(day, 14, GetDate()) AT TIME ZONE 'Central European Standard Time')).

Is there a way how I can combine those in one query? Or fix it in a different way? A XOR would do the trick I guess, but that isn't an option in SOQL.

Answer 1

Take a look at Date Formats and Date Literals. I think you can use NEXT_N_DAYS:n, so your clause would become something as simple as:

Next_Birthday__c > NEXT_N_DAYS:7 AND Next_Birthday__c <= NEXT_N_DAYS:14

Of course don't forget to create Next_Birthday__c formula field, that will calculate the next birthday

EDIT

Of course you can also create Days_Until_Next_Birthday__c formula field and just query based on it. Then your clause would be even simpler:

Days_Until_Next_Birthday__c > 7 AND Days_Until_Next_Birthday__c <= 14