You need to sign in to do that
Don't have an account?
Dagny Fernandes
String Query with Date filter
I Have a Query Which is giveing the below error. below is the sample code and error.
Error : System.QueryException: line 1:497 no viable alternative at character ' '
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 ' '
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
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 descError: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 descError:System.QueryException: value of filter criterion for field 'CreatedDate' must be of type dateTime and should not be enclosed in quotes
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
ie. Date startDt = oppStart.Date_marked_as_settled1 ...
In strQuery... createddate>= :startDt ....etc. ..?
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';