OraFAQ Forum: SQL & PL/SQL » dates question

Home » SQL & PL/SQL » SQL & PL/SQL » dates question

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

dates question [message #574646]

Mon, 14 January 2013 12:22

ora1980
Messages: 243
Registered: May 2008

Senior Member

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 (X) VALUES (TO_DATE('2012-12-04','YYYY-MM-DD'));
 
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 ;

my requirement: Given a input parameter date, find out given a date...

1. What is the mininum (earliest) date for that month and year from table test_g

(input of any date in december 2012 should give december 3 2012)

2. What is the last two dates for that month and year

(input of any date in december 2012 should give two results; 12/16/2012 and 12/31/2012

I used:

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
p_in_date_is_in boolean := false; 
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')
and to_char(x,'YYYY') = TO_CHAR(P_IN_DATE,'YYYY');
  
  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 TO_CHAR(to_date((x,''dd-mon-yyyy''),''MON'') = to_char(to_date('''||P_IN_DATE||''',''dd-mon-yyyy''),''MON'')
             and TO_CHAR(to_date((x,''dd-mon-yyyy''),''YYYY'') = to_char(to_date('''||P_IN_DATE||''',''dd-mon-yyyy''),''YYYY''))
     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||'''');
 
      if trunc(p_in_date) = TO_DATE(frec(i).x,'dd-mon-yyyy') then
          p_in_date_is_in := true;
      end if;
 
     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);
   
  if p_in_date_is_in then
  
   dbms_output.put_line('matches');
  end if;
  
 
end;

I used to_char to extract month and year first and find out the results, but I am getting results
in sql navigator, but errors using plsql developer

how to make sure the format of date entered doesnt matter

I need it in plsql , not sql