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

Question

Is there anyway I can use a variable value for LAST_N_DAYS. For example, I am trying to do something like:

Integer lastDays = 7;
//... 
//...
List<Opportunity> opportunities = [SELECT OwnerId, Amount, Probability FROM OPPORTUNITY 
where Amount > 0 and LastModifiedDate < LAST_N_DAYS:lastDays];

But I get:

expecting a number, found 'lastDays'

Answer 1

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];

Answer 2

You can use Database.query() with a string like:

Integer lastDays = 7;

List<OPPORTUNITY> opplist = Database.query('SELECT OwnerId, Amount, Probability FROM OPPORTUNITY where Amount > 0 and LastModifiedDate < LAST_N_DAYS:' + lastDays);

System.debug(opplist);

This is referred to as Dynamic SOQL.

Answer 3

It would be great if you could, but you can't. At least not in any way I'm aware of. When I need this, I build up dynamic SOQL. The date arithmetic eyescream mentions is another solution, although make sure you do thorough testing as date arithmetic is much more bug-prone than SOQL date literals.

Answer 4

You can directly query it like:

List<Opportunity> opportunities = [SELECT OwnerId, Amount, Probability FROM OPPORTUNITY 
where Amount > 0 and LastModifiedDate < LAST_N_DAYS:7];

No need to declare variable and its value first,