3

Am a trying to find a way to query every Friday of a month passed in to my apex controller. So say we're looking at March, I want my query to return data from the 3rd, 10th, 17th, 24th and 31st. I see that there is a

DAY_IN_MONTH()

function, but this returns the number in that month and not the day itself. Is there an easy way to do this?

Follow up question: How could I create a list of Dates with the passed in month's Fridays. So this would have either 4 or 5 elements each call. I am making a Lightning cmp table header and would like the headers to be the 4 -5 friday days for the selected month but am finding this difficult.

1
  • 1
    Ask your separate question in a separate post.
    – Adrian Larson
    Mar 16, 2017 at 15:35

2 Answers 2

8

As far as how that function can be used in a query, it would look something lie:

SELECT Id FROM MyObject__c WHERE CreatedDate = THIS_MONTH
AND DAY_IN_MONTH(CreatedDate) IN (3, 10, 17, 24, 31)

However, you could instead use DAY_IN_WEEK and save yourself the trouble of calculating which dates you need:

WHERE DAY_IN_WEEK(CreatedDate) = 6

Note that 1 represents Sunday, and 7 represents Saturday. Since Saturday - 1 = Friday, you should use a value of 6

If you wanted to query records created on Fridays in a particular month/year, it would look like:

public static List<MyObject__c> getRecordsCreatedFriday(Integer year, Integer month)
{
    return [
        SELECT ... FROM MyObject__c
        WHERE CALENDAR_YEAR(CreatedDate) = :year
        AND CALENDAR_MONTH(CreatedDate) = :month
        AND DAY_IN_WEEK(CreatedDate) = 6
    ];
}

You could also add an overload that accepts just a date within the month to filter:

public static List<MyObject__c> getRecordsCreatedFriday(Date dateInMonth)
{
    return getRecordsCreatedFriday(dateInMonth.year(), dateInMonth.month());
}

It might just be worth reading up on all Date Functions.

6
  • Oh man I did not see the DAY_IN_WEEK function. This is great. One follow up Q: If i wanted to create a List of Dates with that months friday's only, could I use this? EDIT: Updated above
    – Angelo
    Mar 16, 2017 at 15:31
  • @Angelo If you had List<Date> fridays, you could just do DAY_ONLY(CreatedDate) IN :fridays. Not sure exactly how to interpret your follow up question.
    – Adrian Larson
    Mar 16, 2017 at 15:34
  • If your Salesforce org has a different Start of Week, will this still work? Mar 16, 2017 at 15:36
  • 2
    @JeremyNottingham Unlike the Datetime documentation, which explicitly mentions locales, the documentation on Date Functions does not say anything about it. I take that to mean it does not vary, but it's a good separate question.
    – Adrian Larson
    Mar 16, 2017 at 15:41
  • 3
    @AdrianLarson - Under the hood is there a performance hit using this function or does SF store that day of week value in the underlying table? I would think they store it to improve performance otherwise it would require a full table scan right? I Just Checked it does require a full table scan so would be best to add indexed fields to the query to reduce cost. Without index filters the cost is like 1.9 (In my org) which is bad.
    – Eric
    Mar 16, 2017 at 15:42
1

You can use the DAY_IN_WEEK() method with the value 6 for Friday:

SELECT Id FROM MyObject__c WHERE DAY_IN_WEEK(CreatedDate) = 6
1
  • Sorry. I badly read the documentation.... Mar 16, 2017 at 15:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .