Not verified hands-on. This article was written from primary documentation rather than reproduced in an org, so it carries no tested-release badge. If you run it and something differs, say so in the comments and it gets tested and corrected.

Straight answer: date literals such as LAST_N_DAYS:30 are relative ranges you put in a WHERE clause, and date functions such as CALENDAR_MONTH() extract a component of a date so you can group by it. The catch that causes most wrong reports is time zone: date functions operate on the GMT value of a dateTime field unless you wrap the field in convertTimezone(), so an evening record can land in yesterday’s bucket.

Date literals

They are keywords, not strings, so no quotes:

SELECT Id, Name, CloseDate, Amount
FROM Opportunity
WHERE CloseDate = THIS_QUARTER
AND CreatedDate = LAST_N_DAYS:90

The families you will actually use:

  • Single days: TODAY, YESTERDAY, TOMORROW.
  • Named periods: THIS_WEEK, LAST_MONTH, NEXT_QUARTER, THIS_YEAR, and fiscal equivalents such as THIS_FISCAL_QUARTER which follow your org’s fiscal year settings.
  • Counted ranges: LAST_N_DAYS:n, NEXT_N_MONTHS:n, LAST_N_FISCAL_QUARTERS:n and friends.
  • Single past periods: N_DAYS_AGO:n, N_MONTHS_AGO:n. These mean that one period, not everything since.

The distinction in that last pair is the one people get wrong. LAST_N_DAYS:7 is a range covering the last week and includes today. N_DAYS_AGO:7 is the single day a week ago. Similarly LAST_MONTH is the whole of the previous calendar month, not the last thirty days.

Use literals rather than computing dates in Apex and binding them. They are evaluated server-side, they make the intent of the query obvious, and they do not drift when a batch job runs over midnight.

Date functions

These pull a component out of a date or dateTime so you can group or filter on it:

SELECT CALENDAR_YEAR(CloseDate) yr,
       CALENDAR_MONTH(CloseDate) mth,
       COUNT(Id) deals,
       SUM(Amount) total
FROM Opportunity
WHERE IsWon = true
AND CloseDate = LAST_N_MONTHS:12
GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)
ORDER BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)

The set covers calendar and fiscal periods, plus DAY_IN_WEEK, DAY_IN_MONTH, DAY_IN_YEAR, WEEK_IN_MONTH, WEEK_IN_YEAR, HOUR_IN_DAY and DAY_ONLY. Two details: DAY_IN_WEEK returns 1 for Sunday, and CALENDAR_MONTH returns a number, so grouping by it gives you 1 to 12 with no year attached. Group by year as well or December and last December merge silently.

DAY_ONLY is the one that turns a dateTime into a date, which is what you want for “records per day” from CreatedDate.

The time zone problem

A dateTime field is stored in GMT. Date functions operate on that stored value. So a record created at 22:00 in London during British Summer Time is stored as 21:00 GMT and lands in the same day. One created at 23:30 in New York is stored as the following day in GMT and lands in tomorrow’s bucket.

The fix is convertTimezone(), which converts the value to the running user‘s time zone before the function is applied:

SELECT DAY_ONLY(convertTimezone(CreatedDate)) created,
       COUNT(Id) records
FROM Case
WHERE CreatedDate = LAST_N_DAYS:30
GROUP BY DAY_ONLY(convertTimezone(CreatedDate))
ORDER BY DAY_ONLY(convertTimezone(CreatedDate))

It only applies inside a date function, and it uses the running user’s time zone, which means the same query returns different buckets for a user in Sydney and a user in Chicago. For an operational report that is correct. For a scheduled job producing a company-wide number, it means the result depends on whose context the job runs in, which is worth pinning down deliberately rather than discovering later.

Date fields, as opposed to dateTime fields, have no time component and no time zone. CloseDate is the same day for everyone, and no conversion is needed or possible.

Grouping, and what it gives back

An aggregate query returns AggregateResult objects in Apex, and you read them by alias:

for (AggregateResult ar : results) {
    Integer month = (Integer) ar.get('mth');
    Decimal total = (Decimal) ar.get('total');
}

Alias your aggregate columns. Without an alias you are reading expr0 and expr1, and the day someone adds a column in the middle, the mapping silently shifts. HAVING filters on the aggregate, which is how you find months with more than a threshold rather than filtering rows first.

Frequently asked

Does LAST_N_DAYS include today? Yes. If you want to exclude today, add a condition excluding TODAY explicitly rather than reaching for a different literal.

Can I use a date literal with a bind variable? No. Literals are keywords. If the period is dynamic, build the query string or compute explicit dates in Apex.

Why do fiscal literals disagree with my finance team? They follow the fiscal year configured in Setup. If that was never changed from the default, fiscal and calendar literals return the same thing and nobody notices until year-end.

Can I group by a formula field? Only if the formula is deterministic and of a groupable type. Date formulas usually work. Anything referencing TODAY() does not.