function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
David81David81 

Select records with custom date field within the current month?

I'm writing a trigger and need to count records that match a few criteria.

 

The current query is:

 

 

Integer i = [select count() from Account where Account_Status__c = 'Open' AND Producer_Code__c = :pcode ]; 
 I need to add another criteria that will match records with "Account_Established__c" within the current month?
 
Any quick and easy way to do this?
 
Thanks in advance. 
 
 
 


Best Answer chosen by Admin (Salesforce Developers) 
Rajesh ShahRajesh Shah
Hi,
 
You can also use Date functions in SOQL. Following is an example. Using below, there wont be a need to create custom formula fields also. 
 
Integer mnth = System.Today().MOnth();
Integer yr = System.Today().Year();
Contact[] con = [Select r.Name, r.Id, r.CreatedDate From Contact r where CALENDAR_MONTH(CreatedDate) = :mnth and CALENDAR_YEAR(CreatedDate) = :yr];
System.debug(con.size());
 
Check the following link for further details on date functions in SOQL:
http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql.htm 
  


All Answers
JeriMorrisJeriMorris
Add a new field to your Account object, named Account_Established_Month__c. Make it a formula field, with a value of MONTH(Account_Established__c). Then query on that field.


David81David81
Sounds like a good plan. 
 
Forgive my ignorance though (just getting started with apex), but how do I get the current month in my query? 


David81David81
Think I've got it. Added another formula field to my custom object.
 
 
TEXT(MONTH(TODAY()))&"/"&TEXT(YEAR(TODAY()))
 
 
 


Rajesh ShahRajesh Shah
Hi,
 
You can also use Date functions in SOQL. Following is an example. Using below, there wont be a need to create custom formula fields also. 
 
Integer mnth = System.Today().MOnth();
Integer yr = System.Today().Year();
Contact[] con = [Select r.Name, r.Id, r.CreatedDate From Contact r where CALENDAR_MONTH(CreatedDate) = :mnth and CALENDAR_YEAR(CreatedDate) = :yr];
System.debug(con.size());
 
Check the following link for further details on date functions in SOQL:
http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql.htm 
  
This was selected as the best answer
David81David81
Rajesh,
 
That is perfect! Thanks much.
 
New query is 
Integer open = [select count() from Account where CALENDAR_MONTH(Account_Established__c) = :mnth AND CALENDAR_YEAR(Account_Established__c) = :yr AND Account_Status__c = 'Open' AND Producer_Code__c = :pcode ];