Date comparison issue

Question

I am implementing date functionality where i want to get records date within last 4th calender quarter.How can i achieve this in SOQL?

I have tried with date in LAST_4_QUARTERS, but am getting records from all past 4 quarters. I want to get records from past 4th Quarter only.

Ex: today is 4th quarter 2013, I want to get records from only 4th quarter of 2012.

[select Name from Invoice_Line_Item__c where Invoice_Date__c =: LAST_4_QUARTERS];

Answer 1

Few issues.

1. When using date literals in SOQL, you do not need to use the colon : that you normally use when referring to variables.
2. You should use the literal LAST_N_QUARTERS:n. So, for your case it would be LAST_N_QUARTERS:4.

So your final SOQL should be

[Select Name From Invoice_Line_Item__c Where Invoice_Date__c = LAST_N_QUARTERS:4];

Here is the documentation you can look at

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm

EDIT

In looking closer at your question, it looks like you don't want the past 4 quarters but rather the fourth quarter of a date in the past (in your case, 1 year ago). If you want to do this, then date literals likely wont be the way to go.

I would create new date instances, and use those in your SOQL. Something like this

    Date beginDate = Date.newInstance(2012,10,1);
    Date endDate = Date.newInstance(2012,12,31);
    list<Invoice_Line_Item__c> invoices = [Select Name From Invoice_Line_Item__c Where
         Invoice_Date__c >=: beginDate and Invoice_Date__c <=: endDate];