Basically I want to display records for a Monday through Sunday work-week, but depending on which day the query is run, the results should be consistent for that work week. Examples of what data I want the query to show:

  • Example 1: If I run the Query on Wednesday, I will see results for Mon/Tue/Wed
  • Example 2: If I run the Query on Monday, I will see results from last Mon through Sun
  • Example 3: If I run the Query on Sunday, I will see results from last Mon through today (Sun)
  • What do you have so far? – dphil Dec 4 '14 at 20:47

Assuming the start of your week for you users' locales is Sunday:

Date queryStartDate;
Date queryEndDate;
if ( System.today().daysBetween( System.today().toStartOfWeek() <= 1 ) ) {
   //it's Sunday or Monday, so look to last week for records 
   queryStartDate = (System.today() - 2).toStartOfWeek() + 1;
   queryEndDate = queryStartDate + 7;
} else {
    //it's later than Monday, so calculate for this week
    queryStartDate = System.today().toStartOfWeek() + 1
    queryEndDate = System.today();

[SELECT foo__c FROM My_Object__c WHERE My_Start_Date__c >= : queryStartDate and My_End_Date__c <= : queryEndDate]; 
| improve this answer | |
  • Well, I can't do this in APEX, only the through a command line for a SOQL Query :(. I'm using Conga Composer to pull data into Excel and referencing a SOQL query to do so through that tool. – Jason Dec 4 '14 at 23:39
  • You might have mentioned that in your question. It would be possible to create a formula field that mirrored this logic, which you then use in your SOQL. – greenstork Dec 5 '14 at 0:18

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.