4

We have a requirement to fetch all accounts whose birthday is after 7 days. i.e. Select id from account where personbirthdate = TODAY()+7

I am looking for a where clause that can return this result.

Alternatively: I am using a formula field on account object for the same.

PersonBirthDayAlertAfter7Days__c = IF(DATE(YEAR(TODAY()),MONTH(PersonBirthdate),DAY(PersonBirthdate)) - TODAY() = 7, True, False)

then using this formula field in query:

Select id from Account where PersonBirthDayAlertAfter7Days__c = TRUE

but it creating an issue with bulk data and processing gets very slow. We can't enable custom indexing on formula field because it is non-deterministic(having TODAY() function).

Any help would be greatly appreciated.

Improve this question

2 Answers 2

Reset to default
2

You have two problems. One is that SOQL's Date Formats and Date Literals don't support the precision that you're asking for. They're more range based. The other as you've learned is that formula field can't be indexed. Instead, each record must be tested against the formula to get it's current value when the query runs. There's only one way to speed that process up.

To achieve the functionality that you're looking for, the person's birthday date can't be a formula when your query runs. It needs to be converted to either a string literal or parsed into a date depending on what you'll compare it with in your query when it runs.

You can do that using an additional date field that's a copy of the results of the formula. Once you have that, you can use the BirthdateFormulaResult field to compare with (Today()+7).

For reference, also see Format() in the SOQL reference if needed should you decide to use a text field instead for your formula results field instead (can use Format(Today()+7) for comparison to text).

Improve this answer
2

You can achieve this using following SOQL

Date afterSevenDays= system.today()+7; 
Account List<Account> = [Select Id FROM Account where personbirthdate =:afterSevenDays];

Thanks

Improve this answer
2
  • The above returns ALL records within the coming week, including tomorrow, not just those that are 7 days away.
    – crmprogdev
    Commented Nov 23, 2016 at 13:14
  • I have edited the required code above.
    – Sukesh Kumar
    Commented Nov 23, 2016 at 13:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .