Using Dates in Dynamic SOQL

I often use dynamic SOQL. It's a very useful when trying to satisfy multiple scenarios in a single query, or when building a query from pieces you don't have until runtime. It gets a little hairy keeping the code clean with large queries, but if you use consistent formatting and indentation, the code ends up fairly readable.

When it comes to using dates in dynamic SOQL, you have to convert your dates and datetimes to strings with a very specific format. I can never remember that format and that area of the documentation is very light, so I always end up trying to find where I used it last or searching online. The problem with searching online is that there are several wrong examples high on the search ranks (at least when I search for it).

So, here is the correct syntax if you ever need to use dates in dynamic SOQL...

• date: myDatetime.format('yyyy-MM-dd')
• datetime: myDatetime.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'')

Here is an example that uses both a date and datetime in a dynamic SOQL statement:

list<contact> c = Database.query('SELECT id FROM SomeObj__c WHERE SomeDatetime__c > ' +
myDatetime1.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'') + 
' AND SomeDate__c < ' + 
myDatetime2.format('yyyy-MM-dd') + 
' LIMIT 10');