[SalesForce] replacing LAST_MONTH

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?

Best Answer

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
Related Topic