Soql query to retrieve opportunities between two dates

Question

I am trying to retrieve opportunities created between 01-01-2011 and 06-30-2011.

Select o.CreatedDate, o.Id, o.LastModifiedDate

from Opportunity o 

where   o.CreatedDate > '1/1/2011' and o.CreatedDate <  '12/31/2011'

order by  o.LastModifiedDate

since createdate is a datetime I get an error saying createdDate is DateTime and should not be enclosed in quotes.

Can someone help on how to get this query working? I just want this query to get it running in apex explorer, this is not part of apex code

Answer 1

You can use the following SOQL query to retrieve opportunities between two dates:

Select o.CreatedDate, o.Id, o.LastModifiedDate 

from Opportunity o 

where   o.CreatedDate > 2011-01-01T00:00:00Z and o.CreatedDate < 2011-12-31T00:00:00Z 

Answer 2

In Salesforce SOQL (Salesforce Object Query Language) you must use date literals not string literals for DateTime Fields. Date literals, without using quotes, need to have YYYY-MM-DD format format.

For the query for your question you can now change the conditions of your date as following:

SELECT o.CreatedDate, o.Id, o.LastModifiedDate

FROM Opportunity o  

WHERE o.CreatedDate >= 2011-01-01 AND o.CreatedDate <= 2011-06-30

ORDER BY o.LastModifiedDate

Explanation:

Compare date values as YYYY-MM-DD.

Use operators with the start date, as follows: 01-01-2011 end date, 06-30-2011 opportunity creates have to be brought in using greater than or equal. Deploy the same query in Apex Explorer successfully and without any error based on the DateTime formatting.