[SalesForce] I am getting an error when i run the dynamic SOQL line 1:213 no viable alternative at character ‘ ‘

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?

Best Answer

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).

Related Topic