[SalesForce] SOQL query to find every Friday of month

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.

Best Answer

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.

Update

Borrowing from @highfive's answer, the easiest way to do this is use the DAY_ONLY operator to convert the DateTime.

SELECT Id FROM Case WHERE DAY_ONLY(ClosedDate) >= :startOfMonth
  AND DAY_ONLY(ClosedDate) <= :endOfMonth

Old Answer

So it seems like there are four options. I'd probably go for the last one, which seems the clearest and simplest. In hindsight this question appears subjective and probably not right for the forum =.

First, you can use @sathya's suggestion which works but might be counter-intuitive if you don't understand the date to datetime conversion that's happening.

Date startOfMonth = Date.newInstance(2012,9,1);
Date endOfMonth = Date.newInstance(2012,10,1);
List<Case> cases = [
        select id from Case 
        where closedDate >= :startOfMonth 
        and closedDate <= :endOfMonth];

Second, you can exactly specify the date time window. Which works but feels a little verbose.

DateTime startOfMonthDT = DateTime.newInstance(2012,9,1,0,0,0);
DateTime endOfMonthDT = DateTime.newInstance(2012,9,30,23,59,59);
List<Case> cases = [
        select id from Case 
        where closedDate >= :startOfMonthDT 
        and closedDate <= :endOfMonthDT];

Third, if you're only worrying about the current or last month you can use date literals.

List<Case> cases = [
        select id from Case 
        where closedDate = LAST_MONTH];

Finally, you can use the date functions, which seems like the clearest (to me at least)

Integer month = 9;
Integer year = 2012;
List<Case> cases = [
        select id from Case 
        where CALENDAR_MONTH(closedDate) = :month
        and CALENDAR_YEAR(closedDate) = :year];

[SalesForce] SOQL query filter WHERE by month

Confirming @Eric, your query

List<CustomObj__c> list = [SELECT Name 
                           FROM CustomObj__c 
                           WHERE Expense__Date__c = THIS_MONTH];

is validated by the following:

I tried out a version of your query myself, and it worked fine.

Though this is besides the point (the Date Literals work), there is always the option of "rolling your own" by something like the following:

Date startOfMonth = Date.today().toStartOfMonth();
Date startOfNextMonth = startOfMonth.addMonths(1);
List<CustomObj__c> list = [SELECT Name 
                           FROM CustomObj__c 
                           WHERE Expense__Date__c >= :startOfMonth
                           AND Expense_Date__c < :startOfNextMonth];

Best Answer

Related Solutions

[SalesForce] Using a date for a datetime field in a SOQL Query criteria

Update

Old Answer

[SalesForce] SOQL query filter WHERE by month

Related Topic