Finding a Certain Day of the Week

Hey, Ted:

Our work week ends on Sunday. Individual manufacturing operations are usually scheduled to be completed from Monday through Friday, but occasionally we schedule something for Saturday or Sunday. Using SQL, how can I find the week-ending date for a manufacturing operation? Also, how can I find the first Friday on or after the due date?

--Tim

Use the DAYOFWEEK and DAYOFWEEK_ISO functions to help you find dates that fall on certain days. Both functions return a number from 1 to 7. For DAYOFWEEK, Sunday is day 1. For DAYOFWEEK_ISO, Monday is day 1. Since your week ends on Sunday, the DAYOFWEEK_ISO function is the better choice for your date calculations.

Let's take the first request. To find the end of the week requires you to find the Sunday that is on or after the due date. Here's how it's done.

select duedate, 
       duedate + (7 - dayofweek_iso(duedate)) days 
  from mfgord 

The day of the week is subtracted from 7, giving a number of days remaining until the next Sunday. If the operation is due on a Tuesday, for example, DAYOFWEEK_ISO returns a value of 2, which, when subtracted from 7, yields 5. Adding five days to the Tuesday due date provides the date of the following Sunday.

Here's the output of some sample data to show you how the expression calculated the Sunday dates.

DUEDATE   Date expression
08/15/03     08/17/03    
08/16/03     08/17/03    
08/17/03     08/17/03    
08/18/03     08/24/03    
08/19/03     08/24/03    
08/20/03     08/24/03    
08/21/03     08/24/03    
08/22/03     08/24/03    
08/23/03     08/24/03    
08/24/03     08/24/03    
08/25/03     08/31/03    

To find the Friday on or before the due date requires slight modification to the query. Since Friday is the fifth day of the week, subtract the day of week from 12, which is 7 plus 5, and take the remainder of dividing by 7 to find the number of days to offset the due date.

select duedate, 
       duedate + mod(12 - dayofweek_iso(duedate),7) days
  from mfgord 

If an operation is due on Tuesday, for example, the DAYOFWEEK_ISO operation returns a 2. Subtracting 2 from 12 leaves 10, which, when divided by 7, yields a remainder of 3. Adding three days to a Tuesday date gives a Friday date. See the following example.

DUEDATE   Date expression 
08/15/03     08/15/03     
08/16/03     08/22/03     
08/17/03     08/22/03     
08/18/03     08/22/03     
08/19/03     08/22/03     
08/20/03     08/22/03     
08/21/03     08/22/03     
08/22/03     08/22/03     
08/23/03     08/29/03     
08/24/03     08/29/03     
08/25/03     08/29/03     

You didn't ask for it, but I'll throw in another example for the benefit of readers who may need this calculation. Let's modify the first query slightly. Let's return the Sunday following the due date. If the due date is a Sunday, let's return the Sunday a week later, rather than the same date. Here's the SQL that accomplishes that task.

select duedate, 
       duedate + (7 - dayofweek_iso(duedate + 1 day)) days
  from mfgord 

This query differs from the first one in one respect: the DAYOFWEEK_ISO function receives the day after the due date. If the due date is on a Sunday, the inner expression, DUEDATE + 1 DAY, returns a Monday date. The DAYOFWEEK_ISO function returns a 1, which, subtracted from 7, gives an offset of six days. Adding six days to the due date returns the next Sunday. This is shown in the following query output.

DUEDATE   Date expression 
08/15/03     08/16/03     
08/16/03     08/16/03     
08/17/03     08/23/03     
08/18/03     08/23/03     
08/19/03     08/23/03     
08/20/03     08/23/03     
08/21/03     08/23/03     
08/22/03     08/23/03     
08/23/03     08/23/03     
08/24/03     08/30/03     
08/25/03     08/30/03     

Whenever you have to retrieve a date that falls on a certain day of the week, think first of the DAYOFWEEK and DAYOFWEEK_ISO functions.

--Ted

Sponsored By WORKSRIGHT SOFTWARE

600 Billion That's how much a recent independent study estimated U.S. businesses spend on dirty data. How much of that 600 billion is spent by your company? Cleanse your dirty ZIP Codes and mailing addresses with our software and save big bucks. WorksRight Software, Inc. Phone: 601-856-8337 E-mail: software@worksright.com Web site: www.worksright.com