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
Zahir Basith 4Zahir Basith 4 

how to write soql query to get records where date a >date b

Hello friends, 

Please help me with a simple query to get records if data A is > date B. I tried this soql query in query builder but it give error. 

Select Forecast_Handover__c,Name,Site__c,Forecast_Sales_Date__c from Cbd_plot__c where  Forecast_Handover__c > Forecast_Sales_Date__c

both these forecast date fields are date fields in salesforce I am getting error 'Unknown error parsing query'

but at the same time is I try 

Select Forecast_Handover__c,Name,Site__c,Forecast_Sales_Date__c from Cbd_plot__c where  Forecast_Handover__c > TODAY

its returning me records. 

Many thanks in advance
Best Answer chosen by Zahir Basith 4
Alain CabonAlain Cabon
@Zahir Basith 

It is always surprising at the beginning but you cannot compare two fields like that with a SOQL query directly.

There is a very common (and only) workaround by using a new formula field ( checkbox here for a boolean ).

Select Forecast_Handover__c,Name,Site__c,Forecast_Sales_Date__c from Cbd_plot__c
from Cbd_plot__c 
where  Forecast_Handover_is_greater_than_Forecast_Sales_Date__c = true

 Formula field (checkbox): Forecast_Handover_is_greater_than_Forecast_Sales_Date__c defined with: 

Forecast_Handover__c > Forecast_Sales_Date__c

(poor performance but that is the only way without triggers and more complicated code)

All Answers

Alain CabonAlain Cabon
@Zahir Basith 

It is always surprising at the beginning but you cannot compare two fields like that with a SOQL query directly.

There is a very common (and only) workaround by using a new formula field ( checkbox here for a boolean ).

Select Forecast_Handover__c,Name,Site__c,Forecast_Sales_Date__c from Cbd_plot__c
from Cbd_plot__c 
where  Forecast_Handover_is_greater_than_Forecast_Sales_Date__c = true

 Formula field (checkbox): Forecast_Handover_is_greater_than_Forecast_Sales_Date__c defined with: 

Forecast_Handover__c > Forecast_Sales_Date__c

(poor performance but that is the only way without triggers and more complicated code)
This was selected as the best answer
Ajay K DubediAjay K Dubedi
Hi Zahir,

You can not compare two dates inside a soql query like that but you can do it with if statement. So try the following:

//Bringing all info regarding (including Forecast_Handover__c and Forecast_Sales_Date__c) 
List<Cbd_plot__c> list1 = [Select Forecast_Handover__c,Name,Site__c,Forecast_Sales_Date__c from Cbd_plot__c limit 100]; //adjust limit accordingly.

//Another list for storing data
List<Cbd_plot__c> list2 = new List<Cbd_plot__c>();

//Adding elements to second list where Forecast_Handover__c > Forecast_Sales_Date__c
for(Cbd_plot__c cp : list1){
    if(cp.Forecast_Handover__c > cp.Forecast_Sales_Date__c){
        list2.add(cp);
    }
}

Now your list2 has all elements satisfying above condition.

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Thanks,
Ajay Dubedi
Zahir Basith 4Zahir Basith 4
Thanks to both Alian and Ajay, both your answers were really useful and correct in one sense or the other. 
I am looking for a qucik option here without having to add one more apex class that is scheduled to my limited list of scheduled classes. 
So I am going of the light version. 

Once again thanks for your quick response.