-
how many weekends between 2 dates?
How can I find out how many weekends (sat, sun) between 2 dates?
Thanks,
-
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
);
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
you are correct. my bad for not making my question more clear.
can you please help me with this. it is driving me crazy.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
SELECT count(business_date) business_day
FROM (SELECT TO_DATE ('26-oct-2007', 'dd-MON-yyyy')
+ LEVEL
- 1 business_date
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');
-
Working in an Ora9i+ environment I would certainly use the one you found; nice catch.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|