how many weekends between 2 dates?

# Thread: how many weekends between 2 dates?

1. Member
Join Date
Nov 2000
Posts
198

## how many weekends between 2 dates?

How can I find out how many weekends (sat, sun) between 2 dates?

Thanks,

2. How about something like this?
Code:
```select  to_char("END",'mm/dd/yyyy')         "EndDate",
to_char("START",'mm/dd/yyyy')       "StartDate",
round((("END" - "START") / 7) + .9) "WeekEnds"
from
(
select  (select  next_day((to_date('10/29/2007','mm/dd/yyyy') - 1),'SAT') from dual)  "START" ,
(select  next_day((to_date('11/17/2007','mm/dd/yyyy') - 7),'SUN') from dual)  "END"
from    dual
);```

3. Member
Join Date
Nov 2000
Posts
198
Thanks for your response. if the dates where 10/26/07 (friday) and 10/27/07(sat) the query returns 0. I would like to exclude sat and sun from
the difference between the dates.
for example:
the number of days for 10/26/07 and 10/27/07 = 2 days - weekend days
2 - 1 (10/27/07) = 1

I hope this helps

Thanks again.

4. and... that's correct, the query is doing what's supposed to do.

From Friday 10/26/07 thru Saturday 10/27/07 there are ZERO weekends -no Sunday there :-)))) ; please note that as per your specs you wanted to count Sat-Sun weekends.

5. Member
Join Date
Nov 2000
Posts
198
you are correct. my bad for not making my question more clear.

6. mhhhh, I see... Monday-thru-Friday days in between two dates, am I right?

Didn't have the time to test it in deep but I'm pretty confident the logic is sound. If I missed something feel free to work on it but, if it works as it is please remember to name the block after me ... that would be all the credit I'm looking for
Code:
```select  "BaseStartDate",
"BaseEndDate",
"Delta",
"WeekEnds",
"X",
"Y",
("Delta" - ("WeekEnds" * 2) - "X" - "Y" + 1) "MoTuWeThFrDays"
from
(
select  to_char("BASEDATE_START",'mm/dd/yyyy')      "BaseStartDate",
to_char("BASEDATE_END",'mm/dd/yyyy')        "BaseEndDate",
to_char("END",'mm/dd/yyyy')                 "EndDate",
to_char("START",'mm/dd/yyyy')               "StartDate",
round((("END" - "START") / 7) + .9)         "WeekEnds",
("BASEDATE_END" - "BASEDATE_START")         "Delta",
(select  decode(rtrim(to_char("BASEDATE_END",'DAY')),rtrim('SATURDAY'),1,0) from dual) "X",
(select  decode(rtrim(to_char("BASEDATE_START",'DAY')),rtrim('SUNDAY'),1,0) from dual) "Y"
from
(
select  (select  to_date("BASE_START",'mm/dd/yyyy') from dual) "BASEDATE_START",
(select  to_date("BASE_END",'mm/dd/yyyy') from dual) "BASEDATE_END",
(select  next_day((to_date("BASE_START",'mm/dd/yyyy') - 1),'SAT') from dual)  "START" ,
(select  next_day((to_date("BASE_END",'mm/dd/yyyy') - 7),'SUN') from dual)  "END"
from
(
select  (select  to_char(to_date('11/02/2007','mm/dd/yyyy'),'mm/dd/yyyy') from dual)  "BASE_START",
(select  to_char(to_date('11/12/2007','mm/dd/yyyy'),'mm/dd/yyyy') from dual)  "BASE_END"
from    dual
)
)
);```
Last edited by PAVB; 11-07-2007 at 06:23 PM.

7. Member
Join Date
Nov 2000
Posts
198
Thanks so much! it is working like a charm. I promise I'll use your name too
I also found this query that returns business days as well for > 9i version
FROM (SELECT TO_DATE ('26-oct-2007', 'dd-MON-yyyy')
+ LEVEL
FROM DUAL
CONNECT BY LEVEL <=
TO_DATE ('29-oct-2007', 'dd-MON-yyyy')
- TO_DATE ('26-oct-2007', 'dd-MON-yyyy')
+ 1)
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN');

8. Working in an Ora9i+ environment I would certainly use the one you found; nice catch.

#### Posting Permissions

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