This discussion is archived
10 Replies Latest reply: Jan 9, 2013 8:05 AM by Frank Kulash RSS

oracle dates question

user650888 Newbie
Currently Being Moderated
create table test_g(x date);

insert into test_g values (to_date('01-NOV-2001','DD-MON-YYYY'));

insert into test_g values (to_date('02-NOV-2011','DD-MON-YYYY'));

insert into test_g values (to_date('03-DEC-2012','DD-MON-YYYY'));

insert into test_g values (to_date('12-DEC-2012','DD-MON-YYYY'));

insert into test_g values (to_date('31-DEC-2012','DD-MON-YYYY'));

insert into test_g values (to_date('03-NOV-2012','DD-MON-YYYY'));

insert into test_g values (to_date('06-NOV-2012','DD-MON-YYYY'));

insert into test_g values (to_date('09-NOV-2012','DD-MON-YYYY'));


COMMIT ;


CREATE OR REPLACE PROCEDURE P_G_TEST(P_IN_DATE IN DATE)
IS
v_comp date;
v_comp1 date;

BEGIN

   dbms_output.put_line(' month of paramter  '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
     
  select min(x)
  into v_comp
from test_g
where x <= P_IN_DATE
AND TO_CHAR(x,'MON') = to_char(P_IN_DATE,'MON');
  
  dbms_output.put_line('v_comp: '||v_comp);

END;
The procedure takes in paramter of date type.

I have two goals

1. Given a date, find out the month of that date and look from the table test_g what is
the earliest date. Eg: Given 17-DEC-2012, I print 03-DEC-2012. I am able to achieve it

My question is below:

2. Now, given a date, Find out the month of the date, go back one month and find out
last two oldest dates for that month

Eg: Given 12-DEC-2012 as paramter, I want to see 06-NOV-2012 and 09-NOV-2012

(go back one month (November) and from the table, find out what are the last two
oldest values for that month (November))
  • 1. Re: oracle dates question
    user650888 Newbie
    Currently Being Moderated
    select TO_CHAR(add_months('17-DEC-2012', -1),'MON') FROM DUAL

    goes back a month, not sure how to pick up last two oldest dates of Nov now
  • 2. Re: oracle dates question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way:
    WITH  got_r_num       AS
    (
         SELECT     x
         ,     DENSE_RANK () OVER (ORDER BY  x)     AS r_num
         FROM     test_g
         WHERE     x     >= ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                 , -1
                              )
         AND     x     < TRUNC (p_in_date, 'MONTH')
    )
    SELECT     x
    FROM     got_r_num
    WHERE     r_num     <= 2
    ;
    What do you want if there is a tie, for example, if 2 (or more) rows have exactly the same x, and there is no earlier x in the same month? Perhaps you'll want something like this:
    CREATE OR REPLACE PROCEDURE  prev_month_2_earliest (p_in_date  IN  DATE)
    IS
         d1     DATE;     -- Earlist date from previous month
         d2     DATE;     -- 2nd earliest date from that month
    BEGIN
         WITH  got_r_num       AS
         (
              SELECT     x
              ,     DENSE_RANK () OVER (ORDER BY  x)     AS r_num
              FROM     test_g
              WHERE     x     >= ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                                       , -1
                                              )
                 AND     x     < TRUNC (p_in_date, 'MONTH')
            )
         SELECT     MIN (CASE WHEN r_num = 1 THEN x END)     AS dt1
         ,     MIN (CASE WHEN r_num = 2 THEN x END)     AS dt2
         INTO     d1, d2
         FROM     got_r_num
         WHERE     r_num     <= 2;
    
         dbms_output.put_line (  TO_CHAR (d1, 'DD-Mon-YYYY')
                        || ' = earliest date, '
                        || TO_CHAR (d2, 'DD-Mon-YYYY')
                        || ' = 2nd earliest'
                        );
    
    END prev_month_2_earliest
    ;
    Edited by: Frank Kulash on Jan 8, 2013 2:50 PM
  • 3. Re: oracle dates question
    EdStevens Guru
    Currently Being Moderated
    user650888 wrote:
    create table test_g(x date);
    
    insert into test_g values (to_date('01-NOV-2001','DD-MON-YYYY'));
    
    insert into test_g values (to_date('02-NOV-2011','DD-MON-YYYY'));
    
    insert into test_g values (to_date('03-DEC-2012','DD-MON-YYYY'));
    
    insert into test_g values (to_date('12-DEC-2012','DD-MON-YYYY'));
    
    insert into test_g values (to_date('31-DEC-2012','DD-MON-YYYY'));
    
    insert into test_g values (to_date('03-NOV-2012','DD-MON-YYYY'));
    
    insert into test_g values (to_date('06-NOV-2012','DD-MON-YYYY'));
    
    insert into test_g values (to_date('09-NOV-2012','DD-MON-YYYY'));
    
    
    COMMIT ;
    
    
    CREATE OR REPLACE PROCEDURE P_G_TEST(P_IN_DATE IN DATE)
    IS
    v_comp date;
    v_comp1 date;
    
    BEGIN
    
    dbms_output.put_line(' month of paramter  '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
    P_IN_DATE is declared as a DATE but here you are using it as a string (concatenating it between two other strings). This will force oracle to do an implicit TO_CHAR operation on it, which is never a good idea, as you cannot guarantee the result.

    Better to say

    dbms_output.put_line(' month of paramter '|| to_char(P_IN_DATE,'dd-Mon-yyyy') ||' is '||to_char(P_IN_DATE,'MON'));

    select min(x)
    into v_comp
    from test_g
    where x <= P_IN_DATE
    AND TO_CHAR(x,'MON') = to_char(P_IN_DATE,'MON');
    
    dbms_output.put_line('v_comp: '||v_comp);
    
    END;
    The procedure takes in paramter of date type.

    I have two goals

    1. Given a date, find out the month of that date and look from the table test_g what is
    the earliest date. Eg: Given 17-DEC-2012, I print 03-DEC-2012. I am able to achieve it

    My question is below:

    2. Now, given a date, Find out the month of the date, go back one month and find out
    last two oldest dates for that month

    Eg: Given 12-DEC-2012 as paramter, I want to see 06-NOV-2012 and 09-NOV-2012

    (go back one month (November) and from the table, find out what are the last two
    oldest values for that month (November))
  • 4. Re: oracle dates question
    Warren Tolentino Expert
    Currently Being Moderated
    answer to your question # 2.
    SQL> select x
      2    from (select x,
      3                 row_number() over (order by x desc) rn
      4            from test_g
      5           where x <= to_date('12-DEC-2012','dd-mon-yyyy')
      6             AND TO_CHAR(x,'MON') = to_char(add_months(to_date('12-DEC-2012','dd-mon-yyyy'),-1),'MON'))
      7   where rn < 3
      8  order by x;
    
    X
    -----------
    06-Nov-2012
    09-Nov-2012
    
    SQL> 
  • 5. Re: oracle dates question
    user650888 Newbie
    Currently Being Moderated
    Thanks,

    what is wrong with below, IN requires only comma seperated values in brackets ?
    CREATE OR REPLACE PROCEDURE P_G_TEST(P_IN_DATE IN DATE)
    IS
    v_comp date;
    v_comp1 date;
    
    BEGIN
    
       dbms_output.put_line(' month of paramter  '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
         
      select min(x)
      into v_comp
    from test_g
    where x <= P_IN_DATE
    AND TO_CHAR(x,'MON') = to_char(P_IN_DATE,'MON');
      
      dbms_output.put_line('v_comp: '||v_comp);
      
     IF P_IN_DATE IN (select x
         from (select x,
                       row_number() over (order by x desc) rn
                 from test_g
                 where x <= to_date('12-DEC-2012','dd-mon-yyyy')
                   AND TO_CHAR(x,'MON') = to_char(add_months(to_date('12-DEC-2012','dd-mon-yyyy'),-1),'MON'))
         where rn < 3
       order by x )  THEN
       
       DBMS_OUTPUT.PUT_LINE ('YES');
       ELSE
       DBMS_OUTPUT.PUT_LINE('NO');
       END IF;
    
    END;
    ORA-24344: success with compilation error
    20/38 PLS-00103: Encountered the symbol "(" when expecting one of the following:
    , from
  • 6. Re: oracle dates question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    When used in a SQL statement, the IN operator can have a query as its right operand, like this
    SELECT  *
    FROM    scott.emp
    WHERE   deptno  IN (SELECT ...)
    ;
    But when you use the IN operator in PL/SQL, outside of a SQL statement, then the only thing allowed after the keyword IN is a comma-separated list of expressions, none of which can be queries.

    What are you trying to do, anyway?
    If p_in_date is in December, it will never be one of the dates returned by the query: that query is designed to pick dtes from November only.

    Post the results you want. given the data you posted earlier.
  • 7. Re: oracle dates question
    user650888 Newbie
    Currently Being Moderated
    ok, now i am trying to loop through x using bulk collect so I can use the in operator
    create or replace procedure p_g_test (p_in_date in date) 
    is
    v_strg varchar2(4000);
    type t_cob_date is table of date;
    vt_cob_date t_cob_date;
    begin
    
     select x
         from (select x,
                       row_number() over (order by x desc) rn
                                   from test_g
                 where x <= to_date('12-DEC-2012','dd-mon-yyyy')
                   AND TO_CHAR(x,'MON') = to_char(add_months(to_date('12-DEC-2012','dd-mon-yyyy'),-1),'MON'))
         where rn < 3  bulk collect into vt_cob_date;
         
         if vt_cob_date.count > o then
            for i in vt_cob_date.first..vt_cob_date.last loop
               if i = 1 then
                v_strg := vt_cob_date(i);
               else
                v_strg := v_strg ||','||vt_cob_date(i);
               end if;
            end loop;
         end if;
      
    
    end;   
    getting error

    ORA-24344: success with compilation error
    14/20 PL/SQL: ORA-00933: SQL command not properly ended
    8/2 PL/SQL: SQL Statement ignored
  • 8. Re: oracle dates question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user650888 wrote:
    ok, now i am trying to loop through x using bulk collect so I can use the in operator
    You don't need BULK COLLECT to use the IN operator.
    create or replace procedure p_g_test (p_in_date in date) 
    is
    v_strg varchar2(4000);
    type t_cob_date is table of date;
    vt_cob_date t_cob_date;
    begin
    
    select x
    If you use an INTO clause, including BULK COLLECT INTO, it goes here, after the SELECT clause and before the FROM clause.
    See the PL/SQL manual for syntax and examples.
    from (select x,
    row_number() over (order by x desc) rn
    from test_g
    where x <= to_date('12-DEC-2012','dd-mon-yyyy')
    AND TO_CHAR(x,'MON') = to_char(add_months(to_date('12-DEC-2012','dd-mon-yyyy'),-1),'MON'))
    That will pick dates in November of any year, not just November 2012.
    where rn < 3  bulk collect into vt_cob_date;
    See the 2nd comment above.
    if vt_cob_date.count > o then
    Did you mean to use the number 0, instead of the letter o, above?
    for i in vt_cob_date.first..vt_cob_date.last loop
    if i = 1 then
    v_strg := vt_cob_date(i);
    else
    v_strg := v_strg ||','||vt_cob_date(i);
    end if;
    end loop;
    end if;
    
    
    end;   
    getting error

    ORA-24344: success with compilation error
    14/20 PL/SQL: ORA-00933: SQL command not properly ended
    8/2 PL/SQL: SQL Statement ignored
  • 9. Re: oracle dates question
    user650888 Newbie
    Currently Being Moderated
    I used the below it works, as I have to find out November of 2012 (taking into account the month of input parameter)
    create or replace procedure p_testq(p_in_date in date)
    is
    
    v_comp date;
    v_strg varchar2(200);
    
    i number:=1;
    
    type t_trc is ref cursor;
    trc t_trc;
    
    v_sql varchar2(2000);
    
    -- record to which data goes into
    type t_prec is record(x date);
    prec t_prec;
    
    
    -- plsql table to store data
    type t_frec is table of t_prec index by binary_integer;
    frec t_frec;
    
    -- flow of data, is from v_sql --> plsql record --> plsql table
    
    begin
    
     dbms_output.put_line(' month of paramter  '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
         
      select min(x)
      into v_comp
    from test_g
    where x <= P_IN_DATE
    AND TO_CHAR(x,'MON') = to_char(P_IN_DATE,'MON');
      
      dbms_output.put_line('v_comp: '||v_comp);
    
     
      v_sql :=   ' select x
         from (select x,
                       row_number() over (order by x desc) rn
                 from test_g
                 where x <= to_date('''||p_in_date||''',''dd-mon-yyyy'')
                   AND TO_CHAR(x,''MON'') = to_char(add_months(to_date('''||p_in_date||''',''dd-mon-yyyy''),-1),''MON''))
         where rn < 3
       order by x';
      
      dbms_output.put_line(v_sql);
      open trc for v_sql; -- SQL STMT TO ref cursor
      
       LOOP
                        FETCH trc  INTO prec ; -- ref cursor to plsql record
                    exit when trc%notfound;
         dbms_output.put_line('i :'||i);
         frec(i).x := prec.x; -- plsql record to plsql table
         dbms_output.put_line('frec(i).x: '||frec(i).x);
         if i =1 then
          v_strg :=  frec(i).x;
          else
          v_strg := v_strg ||','||frec(i).x;
         end if;
         i:= i+1;
      END LOOP;
      
       dbms_output.put_line(frec.count);
       
       dbms_output.put_line('v_strg: '||v_strg);
       
      
      
     
    end;
    
     
    question:

    If you give the below block, you see the result


    begin
    p_testq('12-DEC-2012');
    end;

    v_strg has 06-NOV-2012,09-NOV-2012

    I want to see them in single quotes '06-NOV-2012','09-NOV-2012', not sure how to include quotes for frec(i).x which will help me in giving above comma seperated in quotes
  • 10. Re: oracle dates question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user650888 wrote:
    I used the below it works, as I have to find out November of 2012 (taking into account the month of input parameter)
    Then you don't want:
    ...      
    select min(x)
    into v_comp
    from test_g
    where x <= P_IN_DATE
    AND TO_CHAR(x,'MON') = to_char(P_IN_DATE,'MON');
    That will include November, 2011, November 2010, and November of any earlier year as well.
    If you only want November, 2012, then do something like you saw in {message:id=10781997}
    ...     WHERE     x     >= ADD_MONTHS ( TRUNC (p_in_date, 'MONTH')
                                 , -1
                              )
         AND     x     < TRUNC (p_in_date, 'MONTH')
    ...
    v_sql :=   ' select x
    from (select x,
    row_number() over (order by x desc) rn
    from test_g
    where x <= to_date('''||p_in_date||''',''dd-mon-yyyy'')
    AND TO_CHAR(x,''MON'') = to_char(add_months(to_date('''||p_in_date||''',''dd-mon-yyyy''),-1),''MON''))
    where rn < 3
    order by x';
    There's no need for dynamic SQL. You can use variables like p_in_date right in a SQL statement, like you right directly above this.
    If you give the below block, you see the result


    begin
    p_testq('12-DEC-2012');
    end;

    v_strg has 06-NOV-2012,09-NOV-2012

    I want to see them in single quotes '06-NOV-2012','09-NOV-2012', not sure how to include quotes for frec(i).x which will help me in giving above comma seperated in quotes
    If you want single-quotes before and after the dates, then you can user the || operator to include them before and after the dates as you build v_strg.
    Try replacing
    ...
    if i =1 then
    v_strg :=  frec(i).x;
    else
    v_strg := v_strg ||','||frec(i).x;
    end if;
    with
    ...
         if i > 1 then
             v_strg :=  v_strg || ','
         end if;
    
         v_strg := v_strg || ''''
                                || frec(i).x
                    || '''';
    If you want to incklude a single-quote inside a string literal, type 2 of them together.
    ''''
    is a 1-character string literal; that 1 character is a single-quote.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points