Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to get records between two dates my controller method. What am I doing wrong?

public class ReportCreation {
    public List<Account> acc{set;get;}

    public Date fromDate{set;get;}
    public Date toDate{set;get;}
    public ReportCreation(){
        acc=List<Account>();
    }
    public void report(){
        String query='select name,id,industry from Account';
        if(fromDate!=null )
            query=query+' where '+fromdate+' < DATE(createdDate)';
        if(todate!=null )
            query=query+' and DATE(createdDate) < '+todate;
        acc=database.query(query);
    }
}
share|improve this question
up vote 6 down vote accepted

Rather unintuitively, the SOQL function to drop the time from a DateTime is DAY_ONLY so as well as Adrian's point about ordering you will need to use that function.

Note also that dynamic SOQL supports simple bind expressions so you can avoid the risk of the wrong date format ending up in the SOQL (and for strings avoid SOQL injection risks) by using e.g:

query += ' WHERE DAY_ONLY(CreatedDate) >= :fromDate';

as fromDate is a simple expression that is in scope when the query is executed.

share|improve this answer
    
Ha I just assumed they were using the correct date function. Good catch. – Adrian Larson Mar 19 at 20:28

Proper filter syntax is [fieldName][comparisonOperator][value].

Change:

query = query+' where '+fromdate+' < DATE(createdDate)';

To:

query += ' WHERE DAY_ONLY(CreatedDate) >= ' + fromdate;

For reference, take a look at Field Expression Syntax.

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 except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the fieldList.

comparisonOperator
Case-insensitive operators that compare values.

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. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.

share|improve this answer

Look at the resulting query with the to_date not null and the from_date null:

select name,id,industry from Account and DATE(createdDate) < {toDate}

Look valid to you?

Instead, your query needs to be valid with either, both, or neither toDate and fromDate having null values:

public class ReportCreation {
    public List<Account> acc{set;get;}

    public Date fromDate{set;get;}
    public Date toDate{set;get;}

    public ReportCreation(){
        acc = List<Account>();
    }

    public void report(){
        String query="select name,id,industry from Account where 1=1";
        if(fromDate != null)
            query += String.Format(" and {0} <= DAY_ONLY(createdDate)", fromDate);
        if(todate != null)
            query += String.Format(" and DAY_ONLY(createdDate) <= {0}", todate);
        acc=database.query(query);
    }
}
share|improve this answer

my class

public class ReportCreation {
    public List<Account> accTotal{set;get;}   
    public String fromDate{set;get;}
    public string toDate{set;get;}
    public void report(){    
             String query='select name,id,industry from Account';
             if(fromDate!=null&&fromDate!='')
                    query+=' where createdDate >'+fromdate+'T00:00:00.000Z';
                if(todate!=null&&toDate!='' )
                     query+=' and createdDate < '+todate+'T00:00:00.000Z';
                accTotal=database.query(query);
    }
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.