I run into this problem all the time.
I want to write a quite routine in the System Log window to Mass Update some records (eg my previous Mass Delete via System Log window blog post). I want to find all records before a certain date, but SOQL never likes my date format, eg:
select Id from Opportunity where Expiry_Date__c < 2010-01-01
Yes, it is possible to convert it to Timezone format and do it this way:
select Id from Opportunity where Expiry_Date__c < 2008-01-01T00:00:00Z
but I’ve always thought that silly when comparing against a Date field.
So, I eventually figured out that I can do it this way:
select Id, Name, LastLoginDate from User where LastLoginDate > :Date.valueOf('2008-01-01')
Of course, this only works within the context of Apex, such as the System Log window. It won’t work in pure SOQL tools like SOQL Explorer. Here’s an example:
Opportunity opps = [select Id from Opportunity where Expiry_Date__c < :Date.valueOf('2010-01-01')]; System.Debug(opps.size());
The Bottom Line
- Date strings can’t be entered into SOQL
- Option 1: Use Timezone format: 2010-01-01T00:00:00Z
- Option 2: Convert from string: :Date.valueOf(’2010-01-01′)