15

In order to write a where clause against a datetime field, you need to format your dateTime object into the following format:

WHERE SystemModstamp > 2005-10-08T01:02:03Z

How do you correctly convert the dateTime object to a String?

2 Answers 2

19
    DateTime dt = DateTime.now();
    String formattedDt = dt.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');

Update: Another option is to use SOQL dynamic binding, no formatting required.

    DateTime dt = DateTime.now();
    String query = 'SELECT Id FROM User WHERE  SystemModstamp > :dt '
    List<SObject> results = Database.query(query);
3
  • 1
    * I think, with the first method, you will lost the timezone info, JSON.serialize(dt).replace('"', '') can solve it. * with the second method, :dt must be in the same scope with Database.query(soql)
    – Hao Liu
    May 8, 2015 at 7:09
  • The answer here is almost correct. Specifying Z in the formatting string is essentially saying that it is formatted in Zulu. Thus the problem is two fold, one yo have to convert it to Zulu and second, the format hh will provide you with 12 hour. HH will provide you with military time (which is what Salesforce uses on the DB Level). Try this if you need to use dynamic SOQL for the date/time: DateTime dt = System.now(); String formattedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'Zulu'); May 31, 2016 at 23:02
  • Surely formatGmt?
    – Keith C
    Sep 12, 2019 at 9:31
0

Just use datetime variable in the SOQL where clause.

For example:

Datetime dt = DateTime.newInstance(2005,10,8,01,02,03);
List<Account> accounts = [SELECT Id, Name FROM Account WHERE CreatedDate < :dt];
System.debug(accounts.size());
2
  • You can do that if you are using a non-string query. If you want to use Database.query('query string here'); then you need to correctly format the datetime object before using it in a query. I have been using string based queries because I generate the list of all columns at runtime. Is there a way to do that using the [SELECT ...] syntax? (that should probably be a separate SE question) Jan 29, 2014 at 17:05
  • You just update the best answer.
    – Cray Kao
    Jan 29, 2014 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.