16

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.

2 Answers 2

24

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 Commented Dec 22, 2013 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
    Commented Dec 22, 2013 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. Commented Dec 22, 2013 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 Commented May 22, 2014 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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .