+ Start a Discussion

SOQL DATETIME comparisons


I'm trying to do a simple query that compares two DATETIME fields on the same record. Here's the query:

SELECT Id FROM Lead WHERE AddressUpdated__c > AccountManagerUpdated__c

Both of these are custom fields (obviously) but I always get this error and for the life of me I can't figure out why:

MALFORMED_QUERY: ... unexpected token: AccountManagerUpdated__c

After much searching and doc flipping I can't seem to find anything that points at what I'm doing wrong...

Message Edited by ccrawford on 06-01-2007 09:30 PM

the docs on SOQL say under field expression

fieldName The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field—it does not need to be a field in the fieldList.
comparisonOperator See Comparison Operators for a list of valid operators.
value A value used to compare with the value in fieldName. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. For date values, use the formatting listed in Date Formats and Date Literals. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.

If you need to compare 2 fields like that, then you'd need to make a formula field does the comparision or returns the difference between the 2, so that in SOQL you can compare the formula result against a literal value.
Whoops I missed that. Thanks.

Seems kinda silly, but I guess I can make *another* custom field. :)
Here's how I solved running an SOQL Query with date comparison where
  1.  one date is determined outside the query, and
  2. one is a date field of the object
I used Dynamic SOQL (which means the query is evaluated at run time, not at save (&compile) time.

I came up with this after I realized the reason binding with a date doesn't work.
It's that dates are converted to strings, sometimes as so 2008-3-2 (2nd March, 2008).
SOQL would insists the same date to be 2008-03-02. (double digits days, months)

Please notice 3 things in the code (highlighted bold)
  1. Dynamic SOQL using Database.query()
  2. Seperate function to format the date, all date manipulation & formatting to string is done there.
  3. a short look at formatting shows that advanced formatting is only available for Datetime object.

Code that get's me the list of contacts with a passport about to expire in the upcoming 60 days:
private String DateStr; //variable to hold the target date

Public String getTargetDate() {
if (DateStr==null){
DateTime t = DateTime.now();
t = t.addDays(60);
DateStr = t.format('yyyy-MM-dd'); //formatting complies with SOQL
return DateStr;

List<Contact> getQuery_Passport_Expires;

Public List<Contact> getQuery_Passport_Expires() {
if (Query_Passport_Expires == null)
Query_Passport_Expires = Database.query('SELECT c.Id, c.Name FROM Contact c WHERE
Passport_Expires_date__c< '+
' ORDER BY c.Name ASC') ;
return Query_Passport_Expires;


I'm bug testing on a production system where creating a new formula field or writing code is not an option. Not being able to do a simple Date comparsion in SOQL statements is a real limitation, what is the reason for not allowing 2 DateTime fields to be compared?
Due to a production bug I need to extract a set of date based on



Because of this very strange limitation I can't do it in 5 mins.  Unbelievable.   I think this should be fixed in a subsequent release.


Indeed, SOQL is disappointingly weak when you try and do anything a little creative.

You can use the Excel Connector , or the Data Export tool, find the records you want and delete them.


I hope this helps.


Good luck getting SF to fix this, if you look at the upcoming ideas, you'll see that they are not really interested.



All well explained in this blogpost -