You need to sign in to do that
Don't have an account?

Date field comparison in SOQL
I'm trying to compare date fields on an object in an Apex SOQL statement:
Contact[] contacts = [SELECT id,
name,
member_status__c,
last_update__c,
renewal_date__c
FROM Contact
WHERE member_status__c = 'Active' AND renewal_date__c < last_update__c];
Is this possible? I'm getting 'unexpected token' errors with queries like the example above. It appears, the command interpreter is not expecting the second field name. Date functions like TODAY and LAST_X_DAYS work of course.
Contact[] contacts = [SELECT id,
name,
member_status__c,
last_update__c,
renewal_date__c
FROM Contact
WHERE member_status__c = 'Active' AND renewal_date__c < last_update__c];
Is this possible? I'm getting 'unexpected token' errors with queries like the example above. It appears, the command interpreter is not expecting the second field name. Date functions like TODAY and LAST_X_DAYS work of course.
1. get the data out and then compare it
2. create a formula field on the object called "renewal date less than last update" that returns true or false and then use that in your query.
Steve
- one date is determined outside the query, and
- 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)
http://ideas.salesforce.com/article/show/10094718/fix_bug_SOQL_Binding_with_date_variable
Message Edited by mtbclimber on 01-04-2009 05:01 PM
Hi Steve,
I was also facing the same problem and tried to attempt option: 2. And realised formula can't return a boolean type and found no way to convert the boolean to TEXT using the functions.
Can you please tell how can we accomplish ths?
Thanks Much!
I am stuck with the same issue,
Wanted to use system.today() outside APEX.
In dataloader CLI process-conf.xml file.
anyone with any insight please post.
Thanks
Hi
I hope the following helps:
My Select statement looks like this:
Thanks for the quick reply.
I was trying to achieve the same using SOQL in dataloader.
I guess the given two options works with APEX SOQL.
But, with dataloader CLI automated process, I am trying to configure SOQL string in process-conf.xml file as key value pair.
Please advice if any suggestions.
Thanks
Hi, i am also facing the same problem. please look into this
http://boards.developerforce.com/t5/General-Development/Date-comparison-in-Salesforce/m-p/336361#M60661
Prakash
Hi beener
I found another method to use the date in Database.query();
date tempDate = date.today().addDays(60);
String targetdate = String.valueof(tempDate);
Just convert the required date into String before using as condition in Database.query();
cheers
suersh
I hope the following helps:
Contact[] contacts = [SELECT id,
name,
member_status__c,
last_update__c,
renewal_date__c
FROM Contact
WHERE member_status__c = 'Active' AND renewal_date__c <=: last_update__c];