Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Datediff in hours and exclude weekends

    Hello all,

    I have created a function that calculates the difference between 2 days in days.
    ie. if Date 1 = May 2, 2008 and date 2 = May 5, 2008
    Looking at this example the day difference = 3 days. Now my issue is how do I remove the weekends from this count so that the count in this example = 1 and not 3........?

    the part of my logic that calculates Day is
    Code:
    IF DatePart = 'Day' OR DatePart = 'DAY' THEN 
           RETURN trunc((((86400*(Date2-Date1))/60)/60)/24);
        END IF;
    I want to keep everything the same and use my logic and code. Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    your query is overly complicated. Simple do

    Code:
    IF upper(DatePart) = 'DAY' THEN 
           RETURN trunc(Date2-Date1);
    END IF;
    That being said, the following select will return your days

    Code:
    select count(*)
      into days
    from ( select rownum rn
           from all_objects
           where rownum <= date2 - date1+1 )
    where to_char( date1+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );
    
    return days;
    Last edited by beilstwh; 05-05-08 at 15:06.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face

    beilstwh,
    Thanks for this, but the more important part is how do I remove the weekends from the query. This is what I need the most Thanks very much.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    see the edited message above. Sorry I was in the middle of editing it when you responded.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2006
    Posts
    140

    Red face

    beilstwh,
    Could you help me change this logic because I would hate to change my entire query....

    This is what I have and trying to comupte this minus the weekends

    Code:
    (CASE WHEN (from_epoch(Date) - from_epoch(Date2)) >=2 
     OR (From_Epoch(Date1) IS NULL and From_Epoch(Date) > sysdate + 2)  THEN 
     'No' ELSE 'Yes' END)NotMetTarget
    Is there anyway of combining that into this logic?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What does from_epoch do?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Apr 2006
    Posts
    140
    beilstwh,

    The from_epoch just takes an epoch time(number of seconds since Jan 1 1970) and converts that to a date, factoring in GMT...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •