Q

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.

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

Sunday.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close