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
ccrawfordccrawford 

SOQL DATETIME comparisons

Hola,

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

SuperfellSuperfell
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.
ccrawfordccrawford
Whoops I missed that. Thanks.

Seems kinda silly, but I guess I can make *another* custom field. :)
beenerbeener
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< '+
                                                 getTargetDate()+
                                                 ' ORDER BY c.Name ASC') ;
    return Query_Passport_Expires;
}

 
TomCusack29TomCusack29
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?
GerhardNewman2GerhardNewman2
Due to a production bug I need to extract a set of date based on

LastModifiedDate!=CreatedDate

 

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.
beenerbeener

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.

 

 
stan_sfdcstan_sfdc
All well explained in this blogpost - 
https://nextgensalesforce.wordpress.com/2015/11/30/dynamic-soql-query-results-in-local-time/