Re: HELP WITH SQL DATE FUNCTIONS

Daren Janes (daren_at_morgan.ucs.mun.ca) wrote:

: I have a need to calculate the number of Monday..Fridays
: between two dates. There isn't a function that I could see 
: in the documentation. I have it working with a PL/SQL
: procedure that sequentially passes through the days in question	
: and  checks to see if its a Mondat..Friday. This is rather CPU	
: intensive and I was wondering if there is a better way.	

This can be done using date arithmetic. Here is one way to do it. I make no claims to doing it in the best manner.

1. Calculate the inclusive days between the start and end date.

    (enddt - startdt) + 1

2. Add any necessary days to get the starting day to a Sunday and

    the ending day to a Saturday. That way you are working in round     weeks. It also must be the previous Sunday and the following     Saturday.

    Add ( day_of_week_start - 1 ) -- example, if starting on Monday,

         then 2 - 1 = Add one day
    Add ( 7 - day_of_week_end ) -- example, if ending on Monday,

         then 7 - 2 = Add five days

3. Divide the total days from above steps by 7. This will give the

    number of weeks. Multiply this by 5 to get the gross number of     weekdays.

    (totdays / 7) * 5

4. Subtract the number of days added in step 2 which were weekdays,

    which should result in the correct number.

    Sub ( greatest( day_of_week_start - 2, 0 ) ) -- example, if

         starting on a Monday, then Subtract 0 days (you added only
         a Sunday).
    Sub ( greatest( 6 - day_of_week_end, 0 ) )    -- example, if
         starting on a Monday, then subtract 4 days (you added Tues
         through Friday).

There is probably a better way to do this, but it works. It will also work correctly if the start and end day are in the same week, etc. etc.

An SQL script is enclosed below which implements this functionality.

set verify off
column weekdays format 99999999 trunc
column basedays format 99999999 trunc
column adddays format 99999999 trunc
column subdays format 99999999 trunc
column totdays format 99999999 trunc
Select
/* Inclusive days */

  (trunc(to_date('&&enddt', 'DD-MON-YY'))

• trunc(to_date('&&startdt','DD-MON-YY'))) + 1 basedays,
  /* Adder for starting day of week */
  (to_number(to_char(to_date('&&startdt','DD-MON-YY'),'D')) -1) adddays,
  /* Adder for ending day of week */
  (7- to_number(to_char(to_date('&&enddt', 'DD-MON-YY'),'D'))) adddays,
  /* Total number of weekdays in period */
  ( ( ( (trunc(to_date('&&enddt', 'DD-MON-YY')) -trunc(to_date('&&startdt','DD-MON-YY'))) + 1 +(to_number(to_char(to_date('&&startdt','DD-MON-YY'),'D')) -1) +(7- to_number(to_char(to_date('&&enddt', 'DD-MON-YY'),'D'))) ) / 7 ) * 5 ) weekdays,
  /* Subtractor for starting day of week */
  greatest( (to_number(to_char(to_date('&&startdt','DD-MON-YY'),'D')) -2),0) subdays,
  /* Subtractor for ending day of week */
  greatest( (6- to_number(to_char(to_date('&&enddt', 'DD-MON-YY'),'D'))),0) subdays,
  /* The entire calculation */
  ( ( ( ( (trunc(to_date('&&enddt', 'DD-MON-YY')) -trunc(to_date('&&startdt','DD-MON-YY'))) + 1 +(to_number(to_char(to_date('&&startdt','DD-MON-YY'),'D')) -1) +(7- to_number(to_char(to_date('&&enddt', 'DD-MON-YY'),'D'))) ) / 7 ) * 5 )
  • greatest( (to_number(to_char(to_date('&&startdt','DD-MON-YY'),'D')) -2),0)
  • greatest( (6- to_number(to_char(to_date('&&enddt', 'DD-MON-YY'),'D'))),0) ) totdays from dual ; undef startdt undef enddt

This could also be easily implemented in PL/SQL in order to get rid of the reference to the DUAL table. Remember to use TRUNC when doing date arithmetic, otherwise it will return decimal values if a time stamp is attached to the date.

Mark McGregor -- mmcgreg_at_uswnvg.com Received on Thu Mar 04 1993 - 20:41:58 CET