4

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);
    }
}

4 Answers 4

6

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.

1
  • Ha I just assumed they were using the correct date function. Good catch.
    – Adrian Larson
    Commented Mar 19, 2016 at 20:28
2

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.

0

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);
    }
}
0

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);
    }
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .