function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Deepak Venkatesh 6Deepak Venkatesh 6 

Issue with Year Comparison in where clause

Hi All, 
I am new to Salesforce, I am accessing sales force data trough another tool and writing a select query to pull out data for last two years but I am getting error and below is the query:

select  
Account.Name,
RecordType.Name,
Opportunity.Effective_Date__c
from 
Opportunity where 
CALENDAR_YEAR(Opportunity.Effective_Date__c)>=:(THIS_YEAR -:2) 

Please suggest, at any point of time, I need to pull 2 years data starting from Jan 1st like if am in November 2017, I need to pull data from Jan 2015. 

I get syntax error "Bind Variables Allowed in Apex Code" 
Thanks in advance !!!
Ajay K DubediAjay K Dubedi
Hi Deepak,

First of all you can't compare the result of a date function with a date literal in a WHERE clause. The following query doesn't work.
Select 
Account.Name,
RecordType.Name,
Opportunity.Effective_Date__c
from 
Opportunity where 
CALENDAR_YEAR(Opportunity.Effective_Date__c)>=:(THIS_YEAR -:2) 

Try the below code it will help to you to pull out data for last two years: 
Integer vari= System.Today().year();
List<Opportunity> opplist=[ SELECT Name, Account.Name, Effective_Date__c FROM Opportunity WHERE CALENDAR_YEAR(Effective_Date__c)<=:vari-2 ];
"opplist" will contain all your last two year records regarding the Opportunity.
For reference I am also providing you the following link that will help you.
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm
Please mark my answer as a solution if it is helpful. 

Regards,
Ajay
Deepak Venkatesh 6Deepak Venkatesh 6
Hi Ajay, 

Thanks for  the response, I am using the above as is SOQL Custom Query Builder
Integer vari=System.Today().year();
List<Opportunity> opplist=[ SELECT Name, Account.Name, Effective_Date__c FROM Opportunity WHERE CALENDAR_YEAR(Effective_Date__c)<=:vari-2 ];

When I am validating the query I am getting error below: 

MALFORMED_QUERY: unexpected token: Integer

Not sure where I am going wrong, please suggest further 
Ajay K DubediAjay K Dubedi
Hi Deepak,
As you didn't mention the tool that you are using for the query so I have given you the SOQL query.
 
Try this code: 
SELECT[ Account.Name, Opportunity.Effective_Date__c FROM Opportunity WHERE CALENDAR_YEAR(Opportunity.Effective_Date__c)<=:(System.Today().year()-2) ];
If this Query still gives you an error then please let me know exactly which tool you are using to pull out data so that I could help you more accurately.

Regards,
Ajay