Correct Date format for Developer Console Query Editor?

Question

I'm trying to debug a SOQL query in the developer console's query editor. This works

SELECT Site__c, Cluster__c, Date__c, Num_Att_records__c FROM Session__c 
Where Session__c.SiteProgram__r.Name = 'Brattleboro'

I'm having trouble getting the date format right for the following:

SELECT Site__c, Cluster__c, Date__c, Num_Att_records__c FROM Session__c 
Where Session__c.SiteProgram__r.Name = 'Brattleboro' AND Date__c = 2012-11-6

With the date as shown, I get the message "Unknown error parsing query"

If I put the date in quotes, I don't see the the parsing error message, but I get the following message when I press the execute button

"[object Object]: 'Brattleboro' AND Date__c = '2012-11-6' ^ ERROR at Row:1:Column:147 value of filter criterion for field 'Date__c' must be of type date and should not be enclosed in quotes"

I thought it was ok to use YYYY-MM-DD in this context. Do I need to use the full date time format? Or am I bumping into some quirk of the developer console?

Answer 1

Try:

SELECT Site__c, Cluster__c, Date__c, Num_Att_records__c 
FROM Session__c  
Where Session__c.SiteProgram__r.Name = 'Brattleboro' AND Date__c = 2012-11-06

Note the leading 0 on the 06.

The format is YYYY-MM-DD, so you need to pad the extra 0.