[SalesForce] Can you use SOQL Date Literals in Apex

Date Literals are really helpful in SOQL queries. There are times in Apex where it would be really helpful to be able to use the literals or some sort of easy equivalent (not in a SOQL query). Is there any easy way to do this or is the best solution to try and hack to together using Date functions?

An example – let's say I want to know if a particular date was in the last quarter – in SOQL I could do something like:

SELECT Id FROM Opportunity WHERE closedate = LAST_QUARTER

I'd like to be able to do something similar in Apex:

if(o.closedate == LAST_QUARTER){
  //do something
}

It can be tough to figure this out, especially with people using different quarters, locales, etc. The SOQL date literals do a great job and I'd like to be able to reuse that logic in Apex. Yes, I could rewrite it using date functions but it's not a trivial task, especially when you take in to account the idea of it being part of a package and it having to adjust for different customers.

It's mostly useful when trying to find time frames that are harder to define, like weeks and quarters.

I realize something like my example doesn't exist, but I didn't know if there was something similar out there that SFDC has to make these things easier.

Best Answer

There is no way to do this by standard. If you do write the code to do this though I'd encourage you to share it as a utility class on GitHub. If you do so and it proves useful it may well end up being incorporated in platform like ApexLang was.

All the new String methods in the previous release came from open sourced code like this, and once it's part of the platform it no longer contributes excessively to governor limits (i.e. you'd get one statement count increment for calling the method, not one for each line of the method.).

Related Solutions

[SalesForce] Using SOQL apex bind variables for date literals (e.g. LAST_N_DAYS, TODAY, THIS_QUARTER)

Wow, that's an interesting one! I doubt it's allowed though (at least without having to use Dynamic SOQL).

You can always do this instead:

Date d = System.today() - 7;
List<Opportunity> opportunities = [SELECT OwnerId, Amount, Probability FROM OPPORTUNITY 
where Amount > 0 and LastModifiedDate < :d];

[SalesForce] find duplicate records based on multiple fields

This could be base of your query for duplicates:

SELECT COUNT(Id), AccountId, OwnerId, ActivityDate
FROM Event
GROUP BY AccountId, OwnerId, ActivityDate
ORDER BY COUNT(Id) DESC
LIMIT 10

Don't run it yet! It's very likely to timeout and I can imagine it having serious performance issues.

This version is bit better (and it returns the earliest Event that matches the conditions):

SELECT MIN(Id) oldest, COUNT(Id), AccountId accId, OwnerId userId, ActivityDate d
FROM Event
WHERE AccountId != null AND ActivityDate = THIS_MONTH
GROUP BY AccountId, OwnerId, ActivityDate
HAVING COUNT(Id) > 1
ORDER BY COUNT(Id) DESC
LIMIT 10

Keep tweaking it till you're happy it can run in reasonable time. Make the WHERE clause selective, maybe increase the HAVING... The MIN(Id) is an idea for the "winner" record. Maybe you'll decide that newest one should win. Maybe you'll build something that asks the user to decide...

~~Anyway. Let's say that "earliest" Id will be a winner~~ (come to thing about it, we probably shouldn't count in Id's being sequential...).

So you have List<AggregateResult> which you can use to make the detailed query for all Events:

// Id oldestEvent = (Id) results[0].get('oldest');
Id accId = (Id) results[0].get('accId'), userId = (Id) results[0].get('userId');
DateTime actDate = (DateTime) results[0].get('d');

Use these to build SELECT ... FROM Event WHERE AccountId = :accId AND ... (optionally AND Id != oldestEvent if you want to skip my "winner". Or sort them by CreatedDate / ActivityDate DESC if you want to be sure the record you want is first in the result set.

The standard merge statement works only on Accounts, Contacts and Leads. I think you'll have to delete them with your own code, deciding which values to keep in the winner, which should just go... Luckily we can't make lookups to Events so at least you don't have to worry about relationships...

Best Answer

Related Solutions

[SalesForce] Using SOQL apex bind variables for date literals (e.g. LAST_N_DAYS, TODAY, THIS_QUARTER)

[SalesForce] find duplicate records based on multiple fields

Related Topic