Monday, July 11, 2016

How To Fetch Next 6 Months or Last 6 Months Records From Salesforce

If your trying to fetch records from Salesforce based on some date values likes today ,tomorrow,last few days/months/weeks/years or next few days/months/weeks/years we can make use of the Salesforce Date Literals to simplify the SOQL query as much as possible and each literal is a range of time beginning with midnight (12:00:00).



Below sample code will explains you how to build a dynamic SOQL query using NEXT_N_MONTHS and LAST_N_MONTHS date literals to fetch Next 6 months and Last 6 Months records from Salesforce.

Sample Code:


public void fetchOrders()
{
List<Order__c> listOrders = new List<Order__c>();
String selWOStatus ='Test'  // Usually value for this comes from visual force page

String qry ='SELECT Id, Name, Location__c,End_Date__c,Createddate FROM Order__c where 
           Location__r.OwnerId=\''+UserInfo.getUserId()+'\'';
                        
if('All'.equalsIgnoreCase(selWOStatus))
  qry +=' AND (Createddate =LAST_N_MONTHS:6 OR End_Date__c < NEXT_N_MONTHS:6) LIMIT 1000';
  
else if('Open'.equalsIgnoreCase(selWOStatus))
  qry +=' AND (End_Date__c < NEXT_N_MONTHS:6 AND (Status !=\'Closed\' AND 
         Status !=\'Canceled\' AND Status !=\'Converted\')) LIMIT 1000';
    
else
  qry +=' AND (Status =\''+String.escapeSingleQuotes(selWOStatus)+'\' 
         AND Createddate = LAST_N_MONTHS:6) LIMIT 1000';

System.debug('q....'+qry); 

listOrders  = Database.query(qry);

System.debug('listTSOOrders....'+listOrders);

}


Thanks for visiting...Enjoy!

No comments: