DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: how many weekends between 2 dates?

  1. #1
    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. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    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. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Nov 2000
    Posts
    198
    you are correct. my bad for not making my question more clear.
    can you please help me with this. it is driving me crazy.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  7. #7
    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
    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');

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
  •  


Click Here to Expand Forum to Full Width