# Thread: Datediff in hours and exclude weekends

1. Registered User
Join Date
Apr 2006
Posts
140

## 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. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
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 14:06.

3. Registered User
Join Date
Apr 2006
Posts
140
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. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
see the edited message above. Sorry I was in the middle of editing it when you responded.

5. Registered User
Join Date
Apr 2006
Posts
140
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. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
What does from_epoch do?

7. Registered User
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
•