SOQL LAST_N_DAYS:30 vs. Reports: Why Different Results?

Question

I noticed a discrepancy between SOQL queries and Salesforce reports when filtering for the last 30 days. In a Salesforce report, selecting “Last 30 Days” includes records from 2024-11-14 to 2024-12-13, exactly 30 days (including today). However, when I run the following SOQL query:

SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = LAST_N_DAYS:30

it includes records from 2024-11-13 to 2024-12-13, which covers 31 days instead of 30.

Similarly, if I query at the end of November, a report for “Last 30 Days” covers only November, while SOQL also includes the last day of October. I would expect both to return the same range. Why does SOQL’s LAST_N_DAYS:30 return an extra day compared to reports?

Answer

Salesforce handles relative date filters differently in SOQL and reports, causing a one-day discrepancy. SOQL’s LAST_N_DAYS:30 includes today and counts back 30 full days, resulting in 31 days. Reports, however, strictly count 30 days backward while aligning with calendar logic.

Enroll at CRS Info Solutions, a premier Salesforce online training institute. Book your free demo session now and start your journey to mastering Salesforce!

This discrepancy occurs due to how Salesforce defines relative date filters differently in SOQL and reports.

1. SOQL’s LAST_N_DAYS:

30 counts backward including today and includes the entire starting day. This means that if today is 2024-12-13, it counts back 30 full days, starting from 2024-11-13, making it 31 days total.

2. Salesforce Reports’ “Last 30 Days”

Salesforce Reports’ “Last 30 Days” also includes today but behaves differently; it counts backward while excluding the first partial day and only includes a strict 30-day range, resulting in 2024-11-14 to 2024-12-13, exactly 30 days.

Workaround

To align SOQL results with the report logic, adjust your query to exclude today’s extra day:

SELECT Id, Name, CloseDate FROM Opportunity 
WHERE CloseDate = LAST_N_DAYS:29 AND CloseDate != TODAY

This effectively shifts the SOQL range to match the report’s behavior.

Summing Up

SOQL’s LAST_N_DAYS:30 includes today and counts back 30 full days, effectively returning 31 days, whereas Salesforce reports’ “Last 30 Days” strictly count back 30 days without the extra starting day. This difference in logic leads to mismatched results when comparing SOQL queries to reports. To align SOQL with reports, adjusting the query to LAST_N_DAYS:29 AND CloseDate != TODAY ensures an exact 30-day range.

Boost Your Salesforce Skills with Expert Training in Kolkata

Elevate your Salesforce skills with expert-led  Salesforce training in Kolkata, tailored for beginners and professionals alike. Gain hands-on experience through real-world projects and master key Salesforce domains, including Admin, Developer, Integration, CPQ, Marketing Cloud, and Lightning Web Components (LWC).

CRS Info Solutions offers industry-focused Salesforce training with personalized guidance and interactive sessions. Whether you’re starting your Salesforce journey or advancing your career, this comprehensive program equips you with the expertise to excel in the Salesforce ecosystem.

Join CRS Info Solutions for a free demo today and take the first step toward a rewarding career in Salesforce!!!