1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To get days between a given date and sysdate

Discussion in 'SQL PL/SQL' started by kiju2002, Apr 17, 2014.

  1. kiju2002

    kiju2002 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have a requirement as below.

    I have 5 attribute values corresponding to 4 days from the week from Monday to Friday.
    The values in the attribute can be 'Yes' or 'No'.
    If Wednesday and Friday are 'Yes', I need a query to get all the Mondays and Fridays between a given date and (next monday or friday from sysdate whichever is earlier.) Say if sysdate is Wednesday, then till friday.

    Similarly, if the attribribute has Monday, Wednesday and Thursday as 'Yes', then I need to get all the mondays, wednesdays and thursdays from a given date(say 13-Mar-2014) to (say sysdate is 17-APR-2014 i,e Wednesday), then till Wednesday.

    Could anyone help me in writing this query.
     
  2. kiju2002

    kiju2002 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have a requirement as below.

    I have 5 attribute values corresponding to 5 days from the week from Monday to Friday.
    The values in the attribute can be 'Yes' or 'No'.
    If Wednesday and Friday are 'Yes', I need a query to get all the Wednesdays and Fridays between a given date and (next Wednesday or friday from sysdate whichever is earlier.) Say if sysdate is Monday, then till Wednesday.

    Similarly, if the attribute has Monday, Wednesday and Thursday as 'Yes', then I need to get all the mondays, wednesdays and thursdays from a given date(say 13-Mar-2014) to (say sysdate is 17-APR-2014 i,e Wednesday), then till Wednesday.

    Could anyone help me in writing this query.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    if you want that to you helped here, then give an example of the data-source, an example of desirable result., and specify your oracle version
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    A PL/SQL function something like the following could do it. The code is untested and in any event would have to be tweaked. It's not clear for example if the starting and ending dates should be included in your 'between' count. It's also not clear where your 'attributes' are coming from, so I don't address that in the block.

    Code (Text):
    FUNCTION cnt_days_between(p_start   DATE,
                              p_end     DATE)
    RETURN NUMBER
    AS
      v_tot_days    NUMBER
      v_days        NUMBER;
      v_dow         VARCHAR2(3);
    BEGIN
      v_tot_days := p_end - p_start;
      v_days     := 0;

      FOR v_Lp IN 1..v_tot_days LOOP
        v_dow := TO_CHAR(p_start + v_Lp, 'DY');

        CASE v_dow
          WHEN 'MON' THEN
            IF [mon_attribute] = 'Y' THEN
              v_days := v_days + 1;
            END IF;
          WHEN 'TUE' THEN
            IF [tue_attribute] = 'Y' THEN
              v_days := v_days + 1;
            END IF;
          WHEN 'WED' THEN
            IF [wed_attribute] = 'Y' THEN
              v_days := v_days + 1;
            END IF;
          WHEN 'THU' THEN
            IF [thu_attribute] = 'Y' THEN
              v_days := v_days + 1;
            END IF;
          WHEN 'FRI' THEN
            IF [fri_attribute] = 'Y' THEN
              v_days := v_days + 1;
            END IF;
        END CASE;
      END LOOP;

      RETURN v_days;
    END cnt_days_between;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Posting the same incomplete request for assistance isn't the best practice; we need more information to provide you with assistance. An example of the output you want to see, some sample data and create table statements would help tremendously.


    The best helping hand you have is at the end of your own arm; use it to provide more information so we can help.