Ask the Expert

SQL for five days ago, excluding weekends

I need the date which is five days before the current date. For business logic it should exclude Saturday and Sunday.

    Requires Free Membership to View

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.

This was first published in July 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: