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?


2 Answers 2


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

Date Literal

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:


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()));

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
  • It isn't documented anywhere that I can find, but using N_MONTHS_AGO:n does work for me (as well as N_DAYS_AGO, N_WEEKS_AGO, N_QUARTERS_AGO, etc...). These date literals accomplish exactly what the OP wants in a single expression...if you feel safe using a feature of dubious documentation.
    – Derek F
    Commented Feb 13, 2017 at 16:52
  • 1
    @AdrianLarson You misunderstand me, the LAST_N_MONTHS:n date literal is documented (as you linked). I'm saying that instead of using date = LAST_N_MONTHS:6 AND date < LAST_N_MONTHS:5, you can use N_MONTHS_AGO:6 instead. The part I was referring to as being undocumented is the fact that N_MONTHS_AGO:n exists.
    – Derek F
    Commented Feb 13, 2017 at 17:03
  • @DerekF I get it now. Woah. That's pretty cool! Might be worth a separate answer. :) Although I will probably stick to using documented functionality myself.
    – Adrian Larson
    Commented Feb 13, 2017 at 17:04
  • 1
    @DerekF I've submitted feedback on that document. We'll see if they update it.
    – Adrian Larson
    Commented Feb 13, 2017 at 17:13
  • 1
    @JoseCabreraZuniga Yes the whole point is that your initial query used an inclusive filter (<=). I say in my post that you can either use an exclusive filter (<) or change the dates. As your post is written now your logic is correct. Perhaps you see now why significant edits can make it really difficult to post a sensible answer. It may be better in the future to try to leave what you had intact and post an EDIT section.
    – Adrian Larson
    Commented Feb 13, 2017 at 17:52

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);
  • N_MONTHS_AGO relative date filter is documented in Relative Date Values for Filter Criteria and IS NOT equivalent of LAST_N_MONTHS - the difference is that N_MONTHS_AGO returns only the data for a specific month from N months ago while LAST_N_MONTHS is returning all months starting from N months back and up to last month (inclusive)
    – o-lexi
    Commented Feb 13, 2017 at 18:43
  • That documentation is close, but not a complete match (it appears to apply to listviews only, not SOQL. The syntax is also slightly different). If a listview simply issues a SOQL query in the back-end, It might explain why we can use it in SOQL.
    – Derek F
    Commented Feb 13, 2017 at 18:56
  • 1
    @Oleksiy regarding the difference between N_MONTHS_AGO:6 and LAST_N_MONTHS:6, I do not claim that they are equivalent. Rather, my claim is that WHERE Date__c = N_MONTHS_AGO:6 and WHERE Date__c = LAST_N_MONTHS:6 AND Date__c < LAST_N_MONTHS:5 are equivalent.
    – Derek F
    Commented Feb 13, 2017 at 18:57
  • Ohh, I see now... In this case it's preferable to use N_MONTHS_AGO:6.
    – o-lexi
    Commented Feb 13, 2017 at 19:04

You must log in to answer this question.

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