Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ron WildRon Wild 

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.
steve_andersensteve_andersen
you can't do this. there are two solutions:
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
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;
}
beener3beener3
Please promote the following idea, which should solve the problem.

http://ideas.salesforce.com/article/show/10094718/fix_bug_SOQL_Binding_with_date_variable
mtbclimbermtbclimber
Steve is right, you can't compare two field values in SOQL today but you can certainly use date and datetime values as bind variables in SOQL within Apex. Try this, for example try this in the System log or your class:

Code:
System.debug('How many open opportunities with a past close date are there? Answer: ' + [Select count() from opportunity where closeDate < :System.Today() and isClosed = false]);

 





Message Edited by mtbclimber on 01-04-2009 05:01 PM
QuickDEVQuickDEV

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!

 
rajarakrajarak

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

 

 
beener3beener3

Hi

 

I hope the following helps:

 

  1. Try and put Date.today() rather than system.today().
  2. I'm using the following syntax successfully:
date target_date;
target_date = Date.today();

 My Select statement looks like this:

 

[SELECT c.Id, c.Name,c.Card_Expires_date__c FROM Contact c WHERE Card_Expires_date__c < :target_date ORDER BY c.Name ASC]

 

 

 
rajarakrajarak

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

 

 

beener3 wrote:

Hi

 

I hope the following helps:

 

  1. Try and put Date.today() rather than system.today().
  2. I'm using the following syntax successfully:
date target_date;
target_date = Date.today();

 My Select statement looks like this:

 

[SELECT c.Id, c.Name,c.Card_Expires_date__c FROM Contact c WHERE Card_Expires_date__c < :target_date ORDER BY c.Name ASC]

 

 

 

 
Prakash@SFDCPrakash@SFDC

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
suresh.csksuresh.csk

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
ShailendraTiwariShailendraTiwari
Hi
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];