How to Find Previous Friday or Current Friday In SQL

During the years I have worked a lot with Multi Level Marketing (MLM) companies. A common thing for these types of companies is that they pay out commissions once a week, twice a week, once a month, etc.
I have been most used to the type of companies that pay out commissions once a week, and often on a Friday.
This tutorial will show how you how you can find previous Friday or current Friday for any given date.

Here is a possible scenario as an example:

• Our MLM company’s commission week goes from Saturday to Friday
• All orders in our company have an order_date, but they also have a commission_date. The commission_date field has the date of the Friday in the commission week where the order was created
• Our task is to create a report that will show the sales for the previous commission week.

The formula we need to use is actually pretty simple:

SELECT    TRUNC(SYSDATE + 1, 'd') - 2
FROM    dual
;

1. TRUNC(SYSDATE, ‘d’) actually gets the previous Sunday, for a given date. Here it is SYSDATE, which is the current date.
   NOTE: The TRUNC function also truncs/cuts away the time part of the SYSDATE value.
2. Since our commission week starts on a Saturday, one day before previous Sunday, we add a “+1” to make sure we get the right commission week in the end.
3. The “- 2” subtracts two days from previous Sunday…which is previous Friday.

So basically, the SELECT we need to use is something similar to this:

SELECT    SUM(o.sales_amt)
FROM    orders o
WHERE    o.commission_date = TRUNC(SYSDATE + 1, 'd') - 2
;

If we run a report like this we will always get a result for the previous commission week, no matter what day we run the report on.
So, we have actually solved our task, but I thought I should show how the result returned for each of the days within one commission week (as mentioned: Saturday to Friday).

SQL> SELECT    TRUNC(TO_DATE('20111224','YYYYMMDD') + 1, 'd') - 2 AS sat
  2          ,TRUNC(TO_DATE('20111225','YYYYMMDD') + 1, 'd') - 2 AS sun
  3          ,TRUNC(TO_DATE('20111226','YYYYMMDD') + 1, 'd') - 2 AS mon
  4          ,TRUNC(TO_DATE('20111227','YYYYMMDD') + 1, 'd') - 2 AS tue
  5          ,TRUNC(TO_DATE('20111228','YYYYMMDD') + 1, 'd') - 2 AS wed
  6          ,TRUNC(TO_DATE('20111229','YYYYMMDD') + 1, 'd') - 2 AS thu
  7          ,TRUNC(TO_DATE('20111230','YYYYMMDD') + 1, 'd') - 2 AS fri
  8  FROM    dual
  9  ;

SAT         SUN         MON         TUE         WED         THU         FRI
----------- ----------- ----------- ----------- ----------- ----------- -----------
12/23/2011  12/23/2011  12/23/2011  12/23/2011  12/23/2011  12/23/2011  12/23/2011

As you can see, we get the same date for all the dates.
A couple of more things before we end this little tutorial.
Earlier I mentioned something about the importance of adding “+ 1” after the date inside the TRUNC function. Here is what the result would look like if you did not add the “+ 1″:

SQL> SELECT    TRUNC(TO_DATE('20111224','YYYYMMDD'), 'd') - 2 AS sat
  2          ,TRUNC(TO_DATE('20111225','YYYYMMDD'), 'd') - 2 AS sun
  3          ,TRUNC(TO_DATE('20111226','YYYYMMDD'), 'd') - 2 AS mon
  4          ,TRUNC(TO_DATE('20111227','YYYYMMDD'), 'd') - 2 AS tue
  5          ,TRUNC(TO_DATE('20111228','YYYYMMDD'), 'd') - 2 AS wed
  6          ,TRUNC(TO_DATE('20111229','YYYYMMDD'), 'd') - 2 AS thu
  7          ,TRUNC(TO_DATE('20111230','YYYYMMDD'), 'd') - 2 AS fri
  8  FROM    dual
  9  ;

SAT         SUN         MON         TUE         WED         THU         FRI
----------- ----------- ----------- ----------- ----------- ----------- -----------
12/16/2011  12/23/2011  12/23/2011  12/23/2011  12/23/2011  12/23/2011  12/23/2011

We can then see that the previous commission date/week for the first day of our commission week (Saturday), is shown as a week before the date of all the other days. So, the conclusion is that when we use the TRUNC function this way, with a “- 2“, we need to do a “+ 1” to make it be correct for ALL the days in our commission week.
In conclusion I would want to show you what we would need to do if we wanted to show the current Friday/commission_week. This might be interesting if we i.e. have a report that shows the sales so far for orders entered in the current commission week. Here is the select we would use:

SQL> SELECT    TRUNC(TO_DATE('20111224','YYYYMMDD') + 1, 'd') + 5 AS sat
  2          ,TRUNC(TO_DATE('20111225','YYYYMMDD') + 1, 'd') + 5 AS sun
  3          ,TRUNC(TO_DATE('20111226','YYYYMMDD') + 1, 'd') + 5 AS mon
  4          ,TRUNC(TO_DATE('20111227','YYYYMMDD') + 1, 'd') + 5 AS tue
  5          ,TRUNC(TO_DATE('20111228','YYYYMMDD') + 1, 'd') + 5 AS wed
  6          ,TRUNC(TO_DATE('20111229','YYYYMMDD') + 1, 'd') + 5 AS thu
  7          ,TRUNC(TO_DATE('20111230','YYYYMMDD') + 1, 'd') + 5 AS fri
  8  FROM    dual
  9  ;

SAT         SUN         MON         TUE         WED         THU         FRI
----------- ----------- ----------- ----------- ----------- ----------- -----------
12/30/2011  12/30/2011  12/30/2011  12/30/2011  12/30/2011  12/30/2011  12/30/2011

Again you can see that we get exactly the same date for all the days within the current commission week.