15

Using the Salesforce API, I am trying to search for Cases which have a CreatedDate between two specific dates, but I am getting errors when sending my query.

For example:

SELECT Id FROM Case  
WHERE  CreatedDate >= 2013-12-21  AND  CreatedDate <= 2013-12-23

The error I get is:

value of filter criterion for field 'CreatedDate' 
must be of type dateTime and should not be enclosed in quotes

I read the "Date Formats and Date Literals" article and do not see where the issue is in my query.

23

CreatedDate is of DateTime type so your query must use the format from the very top of the article you've linked to: YYYY-MM-DDThh:mm:ssZ (optionally with timezones)

SELECT Id 
FROM Case  
WHERE CreatedDate >= 2013-12-21T00:00:00Z
AND CreatedDate <= 2013-12-23T23:59:59Z

Another example:

SELECT Id
FROM Account
WHERE CreatedDate > 2005-10-08T01:02:03Z
4
  • 1
    At the very top of the article: "Date only YYYY-MM-DD 1999-01-01" @eyescream Dec 22 '13 at 20:37
  • You can get used to it. I prefer to think that Date/Time-related queries are weird in any single DB engine, Oracle forces you to write TO_DATE like there's no tomorrow and don't get me started on MSSQL's magic format "112". The CreatedDate name is bit misleading... If you prefer to query the simple way - make a formula field of type Date maybe and filter on it instead? DATE(YEAR(CreatedDate), MONTH(CreatedDate), DAY(CreatedDate)) should be enough.
    – eyescream
    Dec 22 '13 at 20:47
  • I'm used to SQL, where I can throw a Date without extraneous time information against DateTime fields and have it not have a seizure. Also, I can't take your suggestion because I can't actually modify anything about my company's Salesforce environment - I can only use the API to query things for reporting purposes. Thanks for the help. Dec 22 '13 at 20:51
  • 1
    Also note there is a difference between filtering Date and DateTime. You must filter Date type using 2014-12-31 and DateTime type using 2014-12-31T00:00:00Z May 22 '14 at 22:15
5

Salesforce filtering by date examples:

All these SOQL queries are working for me on Salesforce API version 29

Greater than a certain time:

SELECT count(Id) 
FROM Lead
WHERE createdDate > 2015-02-01T00:00:00Z

Greater than yesterday:

SELECT count(Id)
FROM Lead
WHERE createdDate > YESTERDAY

Greater than 62 days ago:

SELECT id
FROM Lead
WHERE CreatedDate = LAST_N_DAYS:62
LIMIT 100

Greater than last week:

SELECT Id
FROM Lead
WHERE createdDate > LAST_WEEK

This year:

SELECT Id
FROM Lead
WHERE CALENDAR_YEAR(CreatedDate) = 2015

Get the current Year returned:

SELECT CALENDAR_YEAR(CreatedDate)
FROM Opportunity
group by CALENDAR_YEAR(CreatedDate)

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.