ApexSensei

Drills › Query the org (SOQL)

Filter with date literals

Query the org (SOQL) · Intermediate · 18 min · Runs in your Salesforce Developer org · Apex Path

Scenario. A pipeline report needs deals closing soon, deals already past their close date, and deals added today.

Count tagged Opportunities with SOQL date literals such as NEXT_N_DAYS, LAST_N_DAYS and TODAY instead of computed dates.

What you will learn

A date literal is a named date range that Salesforce works out when the query runs, in the user's time zone.

Integer soon = [
    SELECT COUNT() FROM Opportunity
    WHERE CloseDate = NEXT_N_DAYS:30
];

TODAY is today. LAST_N_DAYS:30 is the last 30 days plus today. CloseDate < TODAY means any day before today.

Gotcha. NEXT_N_DAYS:30 starts tomorrow, so it leaves out today.

The task

Count four tagged Opportunities by close date and created date, using SOQL date literals.

Examples:

  • close dates 45 and 5 days ago, and 10 and 45 days from today → Next 30 days: 1 (only the one 10 days away)
  • CloseDate < TODAY → Past due: 2, but CloseDate = LAST_N_DAYS:30 → Last 30 days: 1
  • this run created all four → Created today: 4

Requirements:

  1. Keep the setup. It inserts the four Opportunities, and its Stage line works in any org.
  2. Write four SELECT COUNT() queries. Filter each with Name LIKE :tagPattern so only this run's rows count.
  3. Use date literals, not Date.today(): CloseDate = NEXT_N_DAYS:30, CloseDate < TODAY, CloseDate = LAST_N_DAYS:30 and CreatedDate = TODAY.
  4. Print Next 30 days:, Past due:, Last 30 days: and Created today:, each with its count.

Starter code

String tag = 'AS-Q314-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
String stageName = Opportunity.StageName.getDescribe().getPicklistValues()[0].getValue();
List<Opportunity> seeded = new List<Opportunity>();
for (Integer offset : new List<Integer>{ -45, -5, 10, 45 }) {
    seeded.add(new Opportunity(Name = tag + '-' + offset, StageName = stageName,
        CloseDate = Date.today().addDays(offset)));
}
insert seeded;
String tagPattern = tag + '-%';

Integer nextCount = -1;
Integer pastCount = -1;
Integer lastCount = -1;
Integer createdCount = -1;
System.debug('Next 30 days: ' + nextCount);
System.debug('Past due: ' + pastCount);
System.debug('Last 30 days: ' + lastCount);
System.debug('Created today: ' + createdCount);

When it passes

Only the deal 10 days away is in the next 30 days. Both past deals are before today, but only the one 5 days ago is in the last 30 days. All four were created today.

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ Search safely with dynamic SOQL · Search names with SOSL ›