Thursday, May 24, 2012
[You can see this blog for a more elegant solution ]
This was unknown to me and in past months I tried hard to use Datetime field in Dynamic SOQL query, well after doing lots of permutations and combinations I finally figured it out! which I though was not possible.
Well I guess this is worth sharing so here it is.
You can compare any Datetime field in Dynamic SOQL where query! Just keep in mind the format should be correct. You can use the format method to format datetime field like this where System.Now() is my Datetime field.
System.Now().format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\''));
OR
DateTime dt = System.Now();//initialize datetime with current datetime
String formatedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');//format the datetime to make it Dynamic Soql readySo a sample query to extract all the Accounts where the CreatedDate is less than Today will be
DateTime dt = System.Now();//initialize datetime with current datetimeString formatedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');//format the datetime to make it Dynamic Soql readyDatabase.query('Select Id FROM Account WHERE CreatedDate<'+formatedDt);
DateTime dt = System.Now();
ReplyDeleteString 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
There is more elegant way to do this. I wrote about the same here http://blogforce9.blogspot.in/2012/08/using-variables-in-dynamic-soql.html . So you can do something like
DeletestrStudentName = strStudentName +'%';
String qryString = 'Select Studentname__c, Id__c, start_date__c from course__c Course_Name__c != null AND Start_Date__c=:strStartDate AND Studentname__c LIKE strStudentName';
Database.query(qryString);
DateTime dt = System.Now();
ReplyDeleteString strStartDate = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');
IN my case there is no dt (datetime field) to format. My date field value comes from a variable of VIsualforce page date field (user selects date in Visualforce page). So, it is a String variable to use in the query.
How do I format a string to the required format?
Hi manish, We can directly use variables in Dynamic SOQL and should be pretty easy. Have a look at this blog http://blogforce9.blogspot.in/2012/08/using-variables-in-dynamic-soql.html .
Delete