10 Replies Latest reply: Jan 9, 2013 10:05 AM by Frank Kulash RSS

    oracle dates question

    user650888
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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.