I am getting an error when i run my dynamic SOQL line 1:213 no viable alternative at character ' '

Question

I am generating a dynamic SOQL but when I run my SOQL I am getting an error 1:213 no viable alternative at character ' ' My code

/* 
 * admin approval panal
 */
public class ctrl_admin_approval_panal{

    public List<Approval__c> apprLst {get; set;} // To get the soql result 
    public Approval__c appr {get; set;} // getting the value form Vf page 
    Transient String query ;
    public string startDate;
    public string endDate;

    public ctrl_admin_approval_panal(){
        appr = new Approval__c ();
        apprLst = new List<Approval__c>();        
    }

    // Search button
    public PageReference searchBtn(){
        System.debug('Start Date before '+ appr.Search_Data__c);
        System.debug('End date before ' + appr.End_Date__c);

        query = 'SELECT Name, Approval_Status__c, IP_Address_Save__c, Offer_Name__c, Offer_Name__r.Name FROM Approval__c WHERE Approval_Status__c = False';

        if(appr.User__c != null){             
            query = query + ' and User__c = \'' + appr.User__c + '\'' ; 

            if(appr.IP_Address_Save__c != null){
                query = query + ' and IP_Address_Save__c = \'' + appr.IP_Address_Save__c + '\'' ;
            }

            if(appr.Search_Data__c != null){
                query = query + ' and Work_Done_on__c < ' + appr.Search_Data__c;
                query = query + ' and Work_Done_on__c > ' + appr.End_Date__c;
            }
        }else{
            if(appr.IP_Address_Save__c != null){
                query = query + ' and IP_Address_Save__c = \'' + appr.IP_Address_Save__c + '\''  ;
            }

            if(appr.Search_Data__c != null){
                query = query + ' and Work_Done_on__c < ' + appr.Search_Data__c;
                query = query + ' and Work_Done_on__c > ' + appr.End_Date__c;
            }
        }

        system.debug('Query result ' + query );
        apprLst = Database.query(query );
        system.debug('Approval List ' + apprLst );

        return null;
    }
}

When I try my SOQL in WorkBench I also get an error:

MALFORMED_QUERY: 
Work_Done_on__c < 2013-12-29T15:34:10 and Work_Done_on__c > 2014-02-01T15:34:10
^
ERROR at Row:1:Column:193
line 1:193 no viable alternative at character ' '

My SOQL statement:

SELECT Name, Approval_Status__c, IP_Address_Save__c, Offer_Name__c FROM Approval__c WHERE Approval_Status__c = False and User__c = 'a009000000' and Work_Done_on__c < 2013-12-29T15:34:10 and Work_Done_on__c > 2014-02-01T15:34:10

When I remove the Work_Done_on__c field the SOQL works properly. Does anybody have a solution to this problem?

Answer 1

The correct date/time format must be exactly:

YYYY-MM-DDThh:mm:ss.SSSZ

Where:

• YYYY is exactly a four digit year (0000-4000)
• MM is exactly a two digit month (01-12)
• DD is exactly a two digit day (01-31)
• T is exactly T
• hh is exactly a two digit hour (00-23, 00 is 12 AM)
• mm is exactly a two digit minute (00-59)
• ss is exactly a two digit second (00-59)
• SSS is exactly a three digit millisecond (000-999)
• Z is exactly Z, or a timezone offset formatted as: (+|-)hh:mm, exactly either a plus or minus sign, exactly a two digit hour (00-14), and mm is exactly a two digit minute (00-59); Z is the same as -00:00.

You may find it easier to use dynamic binding instead of trying to format date/time values yourself, due to the stringent requirements thereof. However, dynamic binding doesn't work on all types of variables (e.g. any variable that requires a function call).

Answer 2

Instead of using quotes to take care of strings, etc, use variable binding instead.

More info here: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_variables.htm

Your query would look like:

query = 'SELECT Name, Approval_Status__c, IP_Address_Save__c, Offer_Name__c, Offer_Name__r.Name FROM Approval__c WHERE Approval_Status__c = False';

//sfdcfox pointed out that you can't use complex variables in dynamic query binding..
//So instead, simply assign the fields to temporary variables like so.
Id userId = appr.User__c;
String ipAddressSave = appr.IP_Address_Save__c;
Datetime searchDate = appr.Search_Data__c;  //Datetime type?
Datetime enddate = appr.End_Date__c; //Datetime type?


if(appr.User__c != null){             
    query = query + ' and User__c = :userId' ; 
}

if(appr.IP_Address_Save__c != null){
    query = query + ' and IP_Address_Save__c = :ipAddressSave' ;
}

if(appr.Search_Data__c != null){
    query = query + ' and Work_Done_on__c < :searchDate';
    query = query + ' and Work_Done_on__c > :endDate';
 }