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
    Ask your separate question in a separate post. – Adrian Larson Mar 16 '17 at 15:35
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.

| improve this answer | |
  • 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 '17 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 '17 at 15:34
  • If your Salesforce org has a different Start of Week, will this still work? – Jeremy Nottingham Mar 16 '17 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 '17 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 '17 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
| improve this answer | |
  • Sorry. I badly read the documentation.... – Martin Lezer Mar 16 '17 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.