I need the date which is five days before the current date. For business logic it should exclude Saturday and...
For many situations involving business dates, the best solution is a calendar table. For example, see Difference between two dates using office hours (22 May, 2001).
Presumably holidays do not matter in this instance. Hard to imagine why they would not, when weekends do. But as they do not, we can apply a formula. To develop this formula, let's first explore all possibilities.
If today is Monday, then, counting backwards, we skip over Sunday and Saturday, so Friday is one, Thursday two, and so on, taking us to last Monday as "five days ago." Here the quotes around "five days ago" will mean excluding weekends, and represents the date we are after, not literally five days ago. We will see the distinction in a minute.
If today is Tuesday, then, counting backwards, Monday is one day ago, we skip over Sunday and Saturday, Friday is two, Thursday three, and so on, taking us to last Tuesday as "five days ago."
This pattern—"five days ago" is the same day of the week, a week ago—repeats for Wednesday, Thursday, and Friday.
On Saturday, the pattern breaks. On Saturday, five days ago is Monday. And on Sunday, five days ago is also Monday, since we don't count the Saturday.
To sum up our findings and express them as data:
if today is "5 days ago" is subtract Monday Monday 7 Tuesday Tuesday 7 Wednesday Wednesday 7 Thursday Thursday 7 Friday Friday 7 Saturday Monday 5 Sunday Monday 6
The total days (including weekend days) to subtract from today, to get to the "five days ago" date, is shown in the right column.
Let's say that we number the days of the week with Sunday=1, Monday=2, and so on, to Saturday=7. Listing these weekday numbers against the number of days to subtract, we get:
if today is weekday subtract Monday 2 7 Tuesday 3 7 Wednesday 4 7 Thursday 5 7 Friday 6 7 Saturday 7 5 Sunday 1 6
Here's the formula:
subtract = 7 - 2*(weekday/7) + (weekday-2)/7
Remember, this is the number of days to subtract from today, in order to get to the "five days ago" date. In this formula, division is integer division (i.e., round down). Please don't ask me how this formula was discovered. Let's just say it was trial and error.
How would we use this formula in SQL? Here's an example using MySQL syntax:
select distinct cust.fname , cust.lname , cust.phone from orders as o inner join customers as cust on cust.id = orders.cust_id where o.date_ordered = date_sub( current_date , interval 7 - 2 * floor(dayofweek(current_date)/7) + floor((dayofweek(current_date)-2)/7) day ) and o.date_shipped is null
This query returns customer contact information for customers with orders placed exactly "five days ago" which have not been shipped yet.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading