Datetime field in Dynamic SOQL

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 ready


So 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 datetime
String formatedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');//format the datetime to make it Dynamic Soql ready
Database.query('Select Id FROM Account WHERE CreatedDate<'+formatedDt);



5 comments:

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

    ReplyDelete
    Replies
    1. 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
      strStudentName = 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);

      Delete
  2. DateTime dt = System.Now();
    String 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?

    ReplyDelete
    Replies
    1. 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