Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
mbforcembforce 

Date comparision on SOQL query

Dear all,

 

I have 2 object Course and Student... i want to find the details of the student based on their course start date ,

 

I have written following dynamic query but its giving error for date field. please help

 

DateTime dt = System.Now();
String strStartDate = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');

String qryString = 'Select Studentname__c, Id__c, start_date__c from course__c Course_Name__c != null';

if (!strStudentName.equals(''))
qrystring += ' and Studentname__c LIKE \''+String.escapeSingleQuotes(strStudentName) +'%\'';
if (!strStartDate.equals(''))
qrystring += ' and Start_Date__c = \''+String.escapeSingleQuotes(strStartDate) +'\'';
results = Database.query(qryString);

return null;

}


Getting error.......
System.QueryException: value of filter criterion for field 'Start_Date__c' must be of type date and should not be enclosed in quotes
please let me know how to resolv it

kevin lamkevin lam

If the Start_Date__c field is a Date field, then the format of strStartDate should be YYYY-MM-DD.
mbforcembforce

Thanks for the reply Kevin I have written query as follows

 

String qryString = 'Select Studentname__r.First_Name__c, coursename__r.Name,coursename__r.Start_Date__c,coursename__r.End_Date__c,coursename__r.Course_Fees__c,Studentname__r.Date_of_Joining__c from StudentCourse__c where coursename__r.Course_Name__c != null';
          if (!strStudentName.equals(''))
              qrystring += ' and Studentname__r.First_Name__c LIKE \''+String.escapeSingleQuotes(strStudentName) +'%\'';
          if (!strCourseName.equals(''))
           qryString += ' and coursename__r.Course_Name__c LIKE \''+String.escapeSingleQuotes(strCoursename) +'%\'';
          if (strStartDate != null)
              qrystring += ' and coursename__r.Start_Date__c = '+String.escapeSingleQuotes(strStartDate)+'';
          if (strEndDate != null)
              qrystring += ' and coursename__r.End_Date__c = '+String.escapeSingleQuotes(strEndDate);        
         
         results = Database.query(qryString);

 

 

 

when I am checking the query from debug log i am getting follwing values(for search criteria course name = android, start date = 2013-11-10 and end date = 2013-11-24
 
Select Studentname__r.First_Name__c, coursename__r.Name,coursename__r.Start_Date__c,coursename__r.End_Date__c,coursename__r.Course_Fees__c,Studentname__r.Date_of_Joining__c from StudentCourse__c where coursename__r.Course_Name__c != null and coursename__r.Course_Name__c LIKE 'ANDROID%' and coursename__r.Start_Date__c = 2013-11-10 00:00:00 and coursename__r.End_Date__c = 2013-11-24 00:00:00

 

and getting following error

 

System.QueryException: line 1:391 no viable alternative at character ' '

 

 

variables are defined as follows

    public String strStudentName{get;set;}
    public String strCoursename{get;set;}
    public Date strStartDate{get;set;}
    public Date strEndDate{get;set;}

 

please suggest how can I remove HH:MM:SS part from strStartDate and strEndDate

 

Thanks,

Manish.