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
esysoft esysoftesysoft esysoft 

How to compare two dates in SOQL

Hello i have to compare last created date with created date in soql :
  
SELECT OwnerId, CreatedDate, D_partement__c, Metier__c, Profil__c, Secteur__c, TypeContrat__c, Id, Description, DebuMission__c, Remuneration__c FROM Opportunity WHERE Metier__c NOT IN ('A renseigner') WHERE DATEVALUE(CreatedDate) > DATEVALUE('2014-06-28T22:01:02.000Z') AND IsWon = false ORDER BY CreatedDate LIMIT 2000 OFFSET 0
But it returns a Malformed query, i have to use strtotime or something else to compare ?

Thank you.




 
ahmed kishk 9ahmed kishk 9
Hello,

In your query, there is two where clauses. You need to replace the second one with AND or OR according to logic needed. In addition can you try comparing the date without using DATEVALUE function. Like below example:
  
SELECT OwnerId, CreatedDate
FROM Opportunity 
WHERE CreatedDate > 2014-06-28T22:01:02.000Z

Regards,
Ahmed
Mahesh DMahesh D
Hi

We cannot do this directly in an SOQL, becuase it cannot retrieve the record before comparisions.
we can either split SOQL as Query + if condition.
or create a formula field to update based on this condition. and query based on that formula field. I followed by below process.

EX : Criteria met (checkbox) formula

if(Month(Created)<= Month(Time_Based_Mgmnt__r.End_Date__c  , TRUE , FALSE)-->create this in child object and  now the Query is

[SELECT Id FROM ChildObject WHERE CriteriaMet = TRUE];

There is another example:

http://cloudcatamaran.com/2014/03/compare-fields-in-soql-query/
http://simplyforce.blogspot.com/2011/04/tricks-comparing-two-fields-in-soql.html
http://forcecodes.blogspot.com/2013/02/comparing-two-fields-in-soql.html

Please do let me know if it is useful.

Regards,
Mahesh
Naval Sharma4Naval Sharma4

Hi,

I would recommend you to create a variable.
DateTime myDate = DateTime.newInstance(Year, Month, Day, Hour, Minute, Second);

SELECT OwnerId, CreatedDate, D_partement__c, Metier__c, Profil__c, Secteur__c, TypeContrat__c, Id, Description, DebuMission__c, Remuneration__c FROM Opportunity WHERE Metier__c NOT IN ('A renseigner') CreatedDate) >MyDate AND IsWon = false ORDER BY CreatedDate LIMIT 2000 OFFSET 0