replacing LAST_MONTH

Question

I have some code that depends on LAST_MONTH. Something like:

select  .... date__c .. FROM myTable__c where date__c = LAST_MONTH

This code, as I understand, returns a result set constrained to

firstOfPastMonth <= date__c <= LastOfPastMonth

In order to make this query ready to run for any other month and not only for the last one, I tried to eliminate the use of LAST_MONTH by using variables like:

iniMonth = myToday.addMonths(-1).toStartOfMonth();  
endMonth = iniMonth.addDays(date.daysInMonth((integer) iniMonth.year(), (integer) iniMonth.month()));

where myToday represents a call to a date that belongs to the month just after the needed month. So I got:

select  .... date__c ...
FROM myTable__c
where date__c >= :iniMonth AND
  date__c < :endMonth

Am I missing something?

Answer 1

You might consider some other SOQL Date Literals such as LAST_N_MONTHS.

Date Literal
LAST_N_MONTHS:n

Rage
For the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.

So this query filter will take everything before today until the beginning of the month n months ago. Because of that, you need to also exclude the range of all dates between the beginning of today and the end of that month, in order to get just that particular month. For example, if you wanted to get six months ago instead of last month, you would use:

WHERE Date__c = LAST_N_MONTHS:6
AND Date__c < LAST_N_MONTHS:5

The advantage to using this approach is that it's more portable if you want to run the query somewhere you can't access Apex. For example, it will work just fine in Data Loader.

---

As for the logic you have, you can make it much more concise, and your initial code had two obvious problems, both of which you fixed in some manner before I could respond to your additional questions in the comments:

• You were missing your colon characters (:) before your merged values.
• You were including an extra day at the end of the range (since your filter was inclusive).

There are two ways to fix the second bullet point. You can use a non-inclusive filter (< instead of <=) for the end of your range, as you have done. I would say it's easier to understand if you use the actual dates for the month. Consider for example if you run the code for last month:

Integer n = 1;
Date monthStart = Date.today().addMonths(-n).toStartOfMonth();  
Date monthEnd = monthStart.addDays(Date.daysInMonth(monthStart.year(), monthStart.month()));
system.debug(monthStart);
system.debug(monthEnd);

The above script yields:

[4]|DEBUG|2017-01-01 00:00:00
[5]|DEBUG|2017-02-01 00:00:00

You can get the correct dates and use an inclusive range with something like:

Date monthStart = Date.today().addMonths(-n).toStartOfMonth();
Date monthEnd = monthStart.addMonths(1).addDays(-1);

... WHERE Date__c >= :monthStart AND Date__c < :monthEnd

Answer 2

It isn't officially documented anywhere that I can find, but there are extra Date Literals that you can use of the form N_<date scale>_AGO:n, such as N_DAYS_AGO, N_WEEKS_AGO, N_MONTHS_AGO, N_QUARTERS_AGO, etc...

Of these, N_MONTHS_AGO is the one that you're looking for.

SELECT <some fields>
FROM myTable__c
WHERE date__c = N_MONTHS_AGO:6

This is equivalent to the more documented solution (using LAST_N_MONTHS) that Adrian uses.

If you happened to run the query from either solution today (2017-2-13, for records 6 months ago),the query will filter the results of your query to only include records where Date__c is between 2016-08-01 and 2016-08-31.

The only unfortunate bit with using date literals (both the documented and undocumented ones) is that you can't bind an apex variable to use for the date literal.

In other words, the following WHERE clause won't work

WHERE date__c = N_MONTHS_AGO::someApexIntegerVariable

If you want to use the same query, but with a variable integer n for the date literal, you'll need to use dynamic soql like so

Integer numMonthsAgo = 6;
database.query('SELECT Id FROM MyObject__c WHERE Date__c = N_MONTHS_AGO:' + numMonthsAgo);