How is a DateTime object formatted into a String suitable for a String based SOQL query?

Question

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?

score 19 · Accepted Answer · edited Apr 01 '23 at 06:08

19

    Datetime dt = Datetime.now();
    String formattedDt = dt.formatGmt('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);

edited Apr 01 '23 at 06:08

Benjamin Luehrs

473
4
14

answered Jan 29 '14 at 15:59

edgartheunready

2,935
7
23
38

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 08 '15 at 07: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'); – Jason Hardy May 31 '16 at 23:02

1

Surely formatGmt? – Keith C Sep 12 '19 at 09:31

score 0 · Answer 2 · answered Jan 29 '14 at 16:28

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());

answered Jan 29 '14 at 16:28

Cray Kao

1,405
1
14
27

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)
– edgartheunready Jan 29 '14 at 17:05
You just update the best answer. – Cray Kao Jan 29 '14 at 18:28

How is a DateTime object formatted into a String suitable for a String based SOQL query?

2 Answers2

Linked