function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Internal PartnerInternal Partner 

How to report on Date less or equal the LAST DAY OF THE CURRENT YEAR?

Hi all,

I built a report with report type Opportunities and would like to add a filter saying Close Date <= 31.12.CURRENT YEAR. For this year it would be <= 31.12.2019.

I found in the Salesforce Documentation the following statement:

For example, instead of filtering on Close Date greater than Jan 1, 2017, filter using a relative date: Close Date equals this year.

Attached the link to the documentation:

https://help.salesforce.com/articleView?id=filter_dates_relative.htm&type=5

If I use Close Date <= THIS YEAR I am wondering if this is the correct solution, as THIS YEAR means "Starts at 12:00:00 AM on January 1 of the current year and continues through the end of December 31 of the current year". I just want to tell Salesforce, I want all records with Closed Date <= LAST DAY OF THE CURRENT YEAR without using any formulas.

If using formulas what would be a practical approach here?.
Best Answer chosen by Internal Partner
Alain CabonAlain Cabon
 
SELECT Id FROM Opportunity WHERE CloseDate < NEXT_YEAR
is equivalent to:

SELECT Id FROM Opportunity WHERE CloseDate <= LAST DAY OF THE CURRENT YEAR

To find a value within the range, use =. To find values on either side of the range, use > or <. 

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm
 

All Answers

Alain CabonAlain Cabon
 
SELECT Id FROM Opportunity WHERE CloseDate < NEXT_YEAR
is equivalent to:

SELECT Id FROM Opportunity WHERE CloseDate <= LAST DAY OF THE CURRENT YEAR

To find a value within the range, use =. To find values on either side of the range, use > or <. 

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm
 
This was selected as the best answer
Internal PartnerInternal Partner
Thanks Alain for responding. I know this is a little bit confusing. I guess CloseDate <NEXT_YEAR or CloseDate = THIS YEAR would solve our requirement.