SOQL query for displaying leadsouce type count by month

Question

I'm trying to get the a query setup so it delivers the date for each lead in a format of just the month. Right the filter I have is working fine, but the CreatedDate format returns this rediculous format that I can't get to work in third part apps.

Here's the Query:

SELECT CALENDAR_MONTH(CreatedDate),LeadSource
FROM Lead
WHERE LeadSource AND CreatedDate = THIS_YEAR

It should look in the Lead Object and find the month of the created date and the lead source and filter them by leadsource and this years created dates. Or at least that's what I want. I get an error in the Salesforce Workbence and I have no idea why.

Any help would be great.

Answer 1

If you want to count them as the title of your question suggests you would need an aggregate query of this form:

for (AggregateResult ar : [
        SELECT CALENDAR_MONTH(CreatedDate) m, LeadSource s, COUNT(Id) c
        FROM Lead
        WHERE LeadSource != null AND CreatedDate = THIS_YEAR
        GROUP BY CALENDAR_MONTH(CreatedDate), LeadSource
        ORDER BY CALENDAR_MONTH(CreatedDate), LeadSource
        ) {
    Integer m = (Integer) ar.get('m');
    String s = (String) ar.get('s');
    Integer c = (Integer) ar.get('c');
    ...
}

If you are trying to list them all, then the simplest solution might be to add a custom formula field to Lead that holds the MONTH value of the CreatedDate and return that in your query. (Because CALENDAR_MONTH seems to behave like an aggregate function and requires GROUP BY to be used if it is included in the SELECT list.)