+ 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 ];