You need to sign in to do that
Don't have an account?
Zahir 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
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
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
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)
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
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.