Count Days between two dates and excluding Weekends











up vote
0
down vote

favorite












How do I count days between two dates, excluding weekends?



I have this following data, I can simply getting the TAT Value by just doing ClosingDate - ActivityDate. However, I don't want to consider Saturday/Sunday.



+-------------------------+--------+-------------------------+------+

|      ActivityDate       | TypeID |       ClosedDate        | TAT  |

+-------------------------+--------+-------------------------+------+

| Wednesday, May 02, 2018 |   2502 | Wednesday, May 09, 2018 | 7.00 |

| Monday, May 07, 2018    |   2503 | Thursday, May 10, 2018  | 3.00 |

| Tuesday, May 08, 2018   |   2504 | Friday, May 11, 2018    | 3.00 |

| Wednesday, May 09, 2018 |   2505 | Thursday, May 10, 2018  | 1.00 |

| Thursday, May 10, 2018  |   2506 | Friday, May 11, 2018    | 1.00 |

| Friday, May 11, 2018    |   2507 | Thursday, May 17, 2018  | 6.00 |

| Thursday, May 10, 2018  |   2508 | Tuesday, May 15, 2018   | 5.00 |

| Monday, May 14, 2018    |   2509 | Wednesday, May 16, 2018 | 2.00 |

| Monday, May 14, 2018    |   2510 | Thursday, May 17, 2018  | 3.00 |

+-------------------------+--------+-------------------------+------+


Any help is much appreciated.






sql oracle oracle11g







share|improve this question
























  • Possible duplicate of Calculate business days in Oracle SQL(no functions or procedure)
    – nop77svk
    Nov 12 at 22:15















up vote
0
down vote

favorite












How do I count days between two dates, excluding weekends?



I have this following data, I can simply getting the TAT Value by just doing ClosingDate - ActivityDate. However, I don't want to consider Saturday/Sunday.



+-------------------------+--------+-------------------------+------+

|      ActivityDate       | TypeID |       ClosedDate        | TAT  |

+-------------------------+--------+-------------------------+------+

| Wednesday, May 02, 2018 |   2502 | Wednesday, May 09, 2018 | 7.00 |

| Monday, May 07, 2018    |   2503 | Thursday, May 10, 2018  | 3.00 |

| Tuesday, May 08, 2018   |   2504 | Friday, May 11, 2018    | 3.00 |

| Wednesday, May 09, 2018 |   2505 | Thursday, May 10, 2018  | 1.00 |

| Thursday, May 10, 2018  |   2506 | Friday, May 11, 2018    | 1.00 |

| Friday, May 11, 2018    |   2507 | Thursday, May 17, 2018  | 6.00 |

| Thursday, May 10, 2018  |   2508 | Tuesday, May 15, 2018   | 5.00 |

| Monday, May 14, 2018    |   2509 | Wednesday, May 16, 2018 | 2.00 |

| Monday, May 14, 2018    |   2510 | Thursday, May 17, 2018  | 3.00 |

+-------------------------+--------+-------------------------+------+


Any help is much appreciated.






sql oracle oracle11g







share|improve this question
























  • Possible duplicate of Calculate business days in Oracle SQL(no functions or procedure)
    – nop77svk
    Nov 12 at 22:15













up vote
0
down vote

favorite









up vote
0
down vote

favorite











How do I count days between two dates, excluding weekends?



I have this following data, I can simply getting the TAT Value by just doing ClosingDate - ActivityDate. However, I don't want to consider Saturday/Sunday.



+-------------------------+--------+-------------------------+------+

|      ActivityDate       | TypeID |       ClosedDate        | TAT  |

+-------------------------+--------+-------------------------+------+

| Wednesday, May 02, 2018 |   2502 | Wednesday, May 09, 2018 | 7.00 |

| Monday, May 07, 2018    |   2503 | Thursday, May 10, 2018  | 3.00 |

| Tuesday, May 08, 2018   |   2504 | Friday, May 11, 2018    | 3.00 |

| Wednesday, May 09, 2018 |   2505 | Thursday, May 10, 2018  | 1.00 |

| Thursday, May 10, 2018  |   2506 | Friday, May 11, 2018    | 1.00 |

| Friday, May 11, 2018    |   2507 | Thursday, May 17, 2018  | 6.00 |

| Thursday, May 10, 2018  |   2508 | Tuesday, May 15, 2018   | 5.00 |

| Monday, May 14, 2018    |   2509 | Wednesday, May 16, 2018 | 2.00 |

| Monday, May 14, 2018    |   2510 | Thursday, May 17, 2018  | 3.00 |

+-------------------------+--------+-------------------------+------+


Any help is much appreciated.






sql oracle oracle11g







share|improve this question















How do I count days between two dates, excluding weekends?



I have this following data, I can simply getting the TAT Value by just doing ClosingDate - ActivityDate. However, I don't want to consider Saturday/Sunday.



+-------------------------+--------+-------------------------+------+

|      ActivityDate       | TypeID |       ClosedDate        | TAT  |

+-------------------------+--------+-------------------------+------+

| Wednesday, May 02, 2018 |   2502 | Wednesday, May 09, 2018 | 7.00 |

| Monday, May 07, 2018    |   2503 | Thursday, May 10, 2018  | 3.00 |

| Tuesday, May 08, 2018   |   2504 | Friday, May 11, 2018    | 3.00 |

| Wednesday, May 09, 2018 |   2505 | Thursday, May 10, 2018  | 1.00 |

| Thursday, May 10, 2018  |   2506 | Friday, May 11, 2018    | 1.00 |

| Friday, May 11, 2018    |   2507 | Thursday, May 17, 2018  | 6.00 |

| Thursday, May 10, 2018  |   2508 | Tuesday, May 15, 2018   | 5.00 |

| Monday, May 14, 2018    |   2509 | Wednesday, May 16, 2018 | 2.00 |

| Monday, May 14, 2018    |   2510 | Thursday, May 17, 2018  | 3.00 |

+-------------------------+--------+-------------------------+------+


Any help is much appreciated.






sql oracle oracle11g




sql oracle oracle11g






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 at 7:11









Barbaros Özhan

10.9k71430




10.9k71430










asked Nov 12 at 17:31









rocky09

417




417












  • Possible duplicate of Calculate business days in Oracle SQL(no functions or procedure)
    – nop77svk
    Nov 12 at 22:15


















  • Possible duplicate of Calculate business days in Oracle SQL(no functions or procedure)
    – nop77svk
    Nov 12 at 22:15
















Possible duplicate of Calculate business days in Oracle SQL(no functions or procedure)
– nop77svk
Nov 12 at 22:15




Possible duplicate of Calculate business days in Oracle SQL(no functions or procedure)
– nop77svk
Nov 12 at 22:15












1 Answer
1






active

oldest

votes

















up vote
0
down vote













How about something like this?



I took only a part of your data set. The idea is: check day number (to_char(date, 'd')); if day number of the activity date (AD) is larger than closed date (CD), then remove two additional days (weekends).



This might need to be adjusted if one (or both) of those dates fall into Saturday or Sunday (but, according to your sample data, they don't).



SQL> with test (ad, cd) as

  2    (select date '2018-05-02', date '2018-05-09' from dual union all

  3     select date '2018-05-11', date '2018-05-17' from dual union all

  4     select date '2018-05-10', date '2018-05-15' from dual

  5    ),

  6  day_num as

  7    (select ad, cd,

  8      to_char(ad, 'd') addn, to_char(cd, 'd') cddn,

  9      to_char(ad, 'dy') ady, to_char(cd, 'dy') cdy

 10     from test

 11    )

 12  select ad, cd,

 13    cd - ad - case when addn > cddn then 2 else 0 end diff

 14  from day_num;



AD              CD                    DIFF

--------------- --------------- ----------

02.05.2018, wed 09.05.2018, wed          7

11.05.2018, fri 17.05.2018, thu          4

10.05.2018, thu 15.05.2018, tue          3



SQL>





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53267266%2fcount-days-between-two-dates-and-excluding-weekends%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote













    How about something like this?



    I took only a part of your data set. The idea is: check day number (to_char(date, 'd')); if day number of the activity date (AD) is larger than closed date (CD), then remove two additional days (weekends).



    This might need to be adjusted if one (or both) of those dates fall into Saturday or Sunday (but, according to your sample data, they don't).



    SQL> with test (ad, cd) as
    
  2    (select date '2018-05-02', date '2018-05-09' from dual union all
    
  3     select date '2018-05-11', date '2018-05-17' from dual union all
    
  4     select date '2018-05-10', date '2018-05-15' from dual
    
  5    ),
    
  6  day_num as
    
  7    (select ad, cd,
    
  8      to_char(ad, 'd') addn, to_char(cd, 'd') cddn,
    
  9      to_char(ad, 'dy') ady, to_char(cd, 'dy') cdy
    
 10     from test
    
 11    )
    
 12  select ad, cd,
    
 13    cd - ad - case when addn > cddn then 2 else 0 end diff
    
 14  from day_num;
    

    
AD              CD                    DIFF
    
--------------- --------------- ----------
    
02.05.2018, wed 09.05.2018, wed          7
    
11.05.2018, fri 17.05.2018, thu          4
    
10.05.2018, thu 15.05.2018, tue          3
    

    
SQL>





    share|improve this answer

























      up vote
      0
      down vote













      How about something like this?



      I took only a part of your data set. The idea is: check day number (to_char(date, 'd')); if day number of the activity date (AD) is larger than closed date (CD), then remove two additional days (weekends).



      This might need to be adjusted if one (or both) of those dates fall into Saturday or Sunday (but, according to your sample data, they don't).



      SQL> with test (ad, cd) as
      
  2    (select date '2018-05-02', date '2018-05-09' from dual union all
      
  3     select date '2018-05-11', date '2018-05-17' from dual union all
      
  4     select date '2018-05-10', date '2018-05-15' from dual
      
  5    ),
      
  6  day_num as
      
  7    (select ad, cd,
      
  8      to_char(ad, 'd') addn, to_char(cd, 'd') cddn,
      
  9      to_char(ad, 'dy') ady, to_char(cd, 'dy') cdy
      
 10     from test
      
 11    )
      
 12  select ad, cd,
      
 13    cd - ad - case when addn > cddn then 2 else 0 end diff
      
 14  from day_num;
      

      
AD              CD                    DIFF
      
--------------- --------------- ----------
      
02.05.2018, wed 09.05.2018, wed          7
      
11.05.2018, fri 17.05.2018, thu          4
      
10.05.2018, thu 15.05.2018, tue          3
      

      
SQL>





      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        How about something like this?



        I took only a part of your data set. The idea is: check day number (to_char(date, 'd')); if day number of the activity date (AD) is larger than closed date (CD), then remove two additional days (weekends).



        This might need to be adjusted if one (or both) of those dates fall into Saturday or Sunday (but, according to your sample data, they don't).



        SQL> with test (ad, cd) as
        
  2    (select date '2018-05-02', date '2018-05-09' from dual union all
        
  3     select date '2018-05-11', date '2018-05-17' from dual union all
        
  4     select date '2018-05-10', date '2018-05-15' from dual
        
  5    ),
        
  6  day_num as
        
  7    (select ad, cd,
        
  8      to_char(ad, 'd') addn, to_char(cd, 'd') cddn,
        
  9      to_char(ad, 'dy') ady, to_char(cd, 'dy') cdy
        
 10     from test
        
 11    )
        
 12  select ad, cd,
        
 13    cd - ad - case when addn > cddn then 2 else 0 end diff
        
 14  from day_num;
        

        
AD              CD                    DIFF
        
--------------- --------------- ----------
        
02.05.2018, wed 09.05.2018, wed          7
        
11.05.2018, fri 17.05.2018, thu          4
        
10.05.2018, thu 15.05.2018, tue          3
        

        
SQL>





        share|improve this answer












        How about something like this?



        I took only a part of your data set. The idea is: check day number (to_char(date, 'd')); if day number of the activity date (AD) is larger than closed date (CD), then remove two additional days (weekends).



        This might need to be adjusted if one (or both) of those dates fall into Saturday or Sunday (but, according to your sample data, they don't).



        SQL> with test (ad, cd) as
        
  2    (select date '2018-05-02', date '2018-05-09' from dual union all
        
  3     select date '2018-05-11', date '2018-05-17' from dual union all
        
  4     select date '2018-05-10', date '2018-05-15' from dual
        
  5    ),
        
  6  day_num as
        
  7    (select ad, cd,
        
  8      to_char(ad, 'd') addn, to_char(cd, 'd') cddn,
        
  9      to_char(ad, 'dy') ady, to_char(cd, 'dy') cdy
        
 10     from test
        
 11    )
        
 12  select ad, cd,
        
 13    cd - ad - case when addn > cddn then 2 else 0 end diff
        
 14  from day_num;
        

        
AD              CD                    DIFF
        
--------------- --------------- ----------
        
02.05.2018, wed 09.05.2018, wed          7
        
11.05.2018, fri 17.05.2018, thu          4
        
10.05.2018, thu 15.05.2018, tue          3
        

        
SQL>






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 13 at 5:55









        Littlefoot

        18k51333




        18k51333






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53267266%2fcount-days-between-two-dates-and-excluding-weekends%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            How to send String Array data to Server using php in android

            Image
            0 I have a String array which contains value string array = {6,37,38,837,7,...} and I have also used some string values. I want to send that all string values and string array to my database which is located at server side. When I am click on submit button the data will stored in table. On the PHP side I want to read this array as it is,other thing i will do at background means PHP side. See my below code then you will get it what I am says. Here is my code: public class Hotels extends Activity { // Declare Variables JSONArray jsonarray = null; private static final String TAG_ID = "id"; public static final String TAG_NAME = "name"; public static final String TAG_LOCATION = "location"; public static final String TAG_DESC = "description"; String f_date, l_date;...
            Read more

            Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

            Image
            up vote 5 down vote favorite 2 I would like to make it so that the vertical space below the title of each chapter is the same when using the Bjornstrup package (i.e. so that the descenders do not affect the spacing - as shown by the red arrow in the picture below). I think I'm fairly happy with the spacing around "A chapter", so I'd really like titles without descenders (e.g. "Contents") to have the same vertical spacing as "A chapter" below the title. I would also like to remove the large "0" in the contents title. Any help appreciated, as always. documentclass[11pt,a4paper]{book} % Packages[![enter image description here][1]][1] usepackage[left=3.5cm, right=3.5cm, top=4.3cm, bottom=4.25cm]{geometry} usepackage[utf8]{inputenc} usepackage{libertine} usepackage[libertine]{newtx...
            Read more

            Is anime1.com a legal site for watching anime?

            Image
            up vote 4 down vote favorite 1 Other anime streaming sites are blocking my IP because I'm Iranian while anime1.com isn't. I wanted to know if it's legal to watch/stream anime from this site. Thanks. anime-production share | improve this question edited Nov 27 at 22:38 W. Are 1,599 2 23 asked Nov 27 at 20:36 mhnajafi 23 3 add a comment ...
            Read more