+ Start a Discussion
Dagny FernandesDagny Fernandes 

String Query with Date filter

I Have a Query Which is giveing the below error. below is the sample code and error.
Below veriable are the date values come from VF page which is used in the Query.
Type is date field:
oppStart.Date_Marked_As_Settled1__c
oppEnd.Date_Marked_As_Settled1__c

strQuery = 'select Date_Marked_As_Settled__c,Date_Marked_As_Settled1__c,Opportunity.Name ,Product2.ProductCode,TotalPrice from ';
strQuery = strQuery + 'OpportunityLineItem where Product2.ProductCode!=\'SHIPPING\' and CreatedDate >= '+oppStart.Date_Marked_As_Settled1__c+' and CreatedDate <= '+oppEnd.Date_Marked_As_Settled1__c+' and Mark_As_Settled__c=true order by createdDate desc';
getOppLineList();

public void getOppLineList(){

for(OpportunityLineItem c : Database.query(strQuery)){ //Error in this line

}

Error : System.QueryException: line 1:497 no viable alternative at character ' '
 
Best Answer chosen by Dagny Fernandes
Dagny FernandesDagny Fernandes
Thanks @UC Innovation and @Jim

The Blow code worked I am using the below formate.

Date sDate = oppStart.Date_Marked_As_Settled1__c;
        Datetime sdt = datetime.newInstance(sDate.year(), sDate.month(),sDate.day());
        String startTimeFormat = sdt.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');

Strring strQuery = 'select id,TotalPrice from ';        strQuery = strQuery + 'OpportunityLineItem where Product2.ProductCode!=\'SHIPPING\' and CreatedDate >= '+startTimeFormat+'  order by createdDate desc';
                

All Answers

UC InnovationUC Innovation
Maybe those fields are empty/null? Try adding a check for null on those fields before you create the query string.
Dagny FernandesDagny Fernandes
Thanks for the Quick Reply  "UC Innonations"

I have added the Debug log and checked the valuse are Populating the variables.
oppStart.Date_Marked_As_Settled1__c
oppEnd.Date_Marked_As_Settled1__c
 
  • Here in this Query String I hevent added the Quotes to the Variable, values are getting populated: Below is the Debug and Error
MystrQuery==>select id,Date_Marked_As_Settled__c,Date_Marked_As_Settled1__c,Mark_As_Settled__c,Status__c,Refresh_Commission__c,TotalPrice from OpportunityLineItem where Product2.ProductCode!='SHIPPING' and CreatedDate >= 2016-10-28 00:00:00 and CreatedDate <= '2016-10-28 00:00:00' and Mark_As_Settled__c=true order by createdDate desc

Error:System.QueryException: line 1:497 no viable alternative at character ' '
 
  • When I added the Quotes to Variables in that Query sting: Below is the Debug and Error
MystrQuery==>select id,Date_Marked_As_Settled__c,Date_Marked_As_Settled1__c,Mark_As_Settled__c,Status__c,Refresh_Commission__c,TotalPrice from OpportunityLineItem where Product2.ProductCode!='SHIPPING' and CreatedDate >= '2016-10-28 00:00:00' and CreatedDate <= '2016-10-28 00:00:00' and Mark_As_Settled__c=true order by createdDate desc

Error:System.QueryException: value of filter criterion for field 'CreatedDate' must be of type dateTime and should not be enclosed in quotes
UC InnovationUC Innovation
You need to format the string into a datetime that's the same as CreatedDate. 

Try using the DateTime class's parse method:

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_datetime.htm#apex_System_Datetime_parse

Here's an example of what it should look like:

http://salesforce.stackexchange.com/questions/8896/using-a-date-for-a-datetime-field-in-a-soql-query
Jim JamJim Jam
Can't you just use variables instead?
ie. Date startDt = oppStart.Date_marked_as_settled1 ...

In strQuery... createddate>= :startDt   ....etc. ..?
Dagny FernandesDagny Fernandes
Thanks @UC Innovation and @Jim

The Blow code worked I am using the below formate.

Date sDate = oppStart.Date_Marked_As_Settled1__c;
        Datetime sdt = datetime.newInstance(sDate.year(), sDate.month(),sDate.day());
        String startTimeFormat = sdt.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');

Strring strQuery = 'select id,TotalPrice from ';        strQuery = strQuery + 'OpportunityLineItem where Product2.ProductCode!=\'SHIPPING\' and CreatedDate >= '+startTimeFormat+'  order by createdDate desc';
                
This was selected as the best answer