Home » SQL & PL/SQL » SQL & PL/SQL » dates question
dates question [message #574646] Mon, 14 January 2013 12:22 Go to next message
ora1980
Messages: 246
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
Re: dates question [message #574648 is a reply to message #574646] Mon, 14 January 2013 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
>how to make sure the format of date entered doesnt matter
but format of input date DOES matter

what is date for below entered string?

10/11/12
I'll give you 6 guesses; since the first 5 will be wrong!
Re: dates question [message #574649 is a reply to message #574646] Mon, 14 January 2013 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
(input of any date in december 2012 should give two results; 12/16/2012 and 12/31/2012


There is no 12/16 in your data.

SQL> def input=to_date('10/12/2012','DD/MM/YYYY')
SQL> with 
  2    data as (
  3      select x,
  4             rank() over (order by x) rn1,
  5             dense_rank() over (order by x desc) rn2
  6      from test_g
  7      where x between trunc(&input, 'MONTH') and last_day(&input)
  8    )
  9  select *
 10  from data
 11  where rn1 = 1 or rn2 <= 2
 12  order by 1
 13  /
X                  RN1        RN2
----------- ---------- ----------
03-DEC-2012          1          4
12-DEC-2012          3          2
31-DEC-2012          4          1

Regards
Michel
Re: dates question [message #574650 is a reply to message #574646] Mon, 14 January 2013 12:31 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
That looks like too much code to me.

Without getting into specifics or giving you code, it is very easy to find the current month with trunc(sysdate,'MM'). From there you should be able to easily find the:
MIN(date)
MAX(date)
MAX(date) less than the MAX(DATE)
Re: dates question [message #574652 is a reply to message #574650] Mon, 14 January 2013 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
https://forums.oracle.com/forums/thread.jspa?messageID=10791986#10791986
Re: dates question [message #574669 is a reply to message #574649] Mon, 14 January 2013 15:23 Go to previous messageGo to next message
ora1980
Messages: 246
Registered: May 2008
Senior Member
TEST DATA


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('14-NOV-2012','DD-MON-YYYY'));

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

insert into test_g values (to_date('22-11-2012','DD-MM-YYYY'));

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

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

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

insert into test_g values (to_date('2001-11-25','YYYY-MM-DD'));
 
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 ;



I am trying to write a procedure that takes input parameter, checks if the input parameter is either
the earliest date (from test_g) for the given month / year, or one of the latest two dates from test_g

I tried the following:

select min(x)
from test_g
where trunc(x,'MM') = TRUNC(TO_DATE('11/14/2001','MM/DD/YYYY'),'MM');

gives correct result: 11/06/2001

I try the same in plsql, I get invalid month error (ORA-01843)

CREATE OR REPLACE PROCEDURE PTEST(p_in_date date)
IS
v_min date;
v_max1 date;
v_max2 date;

v_b1 boolean:= FALSE;
v_b2 boolean := FALSE;
BEGIN
  
v_b1 := FALSE;
v_b2 := FALSE;
  
dbms_output.put_line('p_in_date: '||p_in_date);
  
select min(x)
into v_min
from test_g
where trunc(x,'MM') = TRUNC(TO_DATE(p_in_date,'MM/DD/YYYY'),'MM');

-- GETTING ORA-01843 NOT A VALID MONTH FOR ABOVE

IF TRUNC(v_min) = trunc(p_in_date) then
  v_b1 := true;
end if;

if v_b1 then
  dbms_output.put_line('yes1');
  
end if;

dbms_output.put_line('v_min: '||v_min);

-- here i want to check if p_in_date is either equal to the earliest entry in test_g for the given month / year
-- or one of the latest two dates for the given month / year, eg: if p_in_date is any month in Nov 2001, check if
-- p_in_date is eihter 11/14/2001 or 11/25/2001



END;


Re: dates question [message #574670 is a reply to message #574669] Mon, 14 January 2013 15:34 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
NEVER use TO_DATE on a DATE datatype; which P_IN_DATE is.
Re: dates question [message #574671 is a reply to message #574669] Mon, 14 January 2013 15:35 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

ora1980 wrote on Tue, 15 January 2013 01:23

CREATE OR REPLACE PROCEDURE PTEST(p_in_date date)
...
where trunc(x,'MM') = TRUNC(TO_DATE(p_in_date,'MM/DD/YYYY'),'MM');

Don't do to_date with date variable - it cause implicit conversion to varchar2
Re: dates question [message #574672 is a reply to message #574671] Mon, 14 January 2013 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
where trunc(x,'MM') = TRUNC(p_in_date,'MM');
Re: dates question [message #574674 is a reply to message #574672] Mon, 14 January 2013 16:03 Go to previous messageGo to next message
ora1980
Messages: 246
Registered: May 2008
Senior Member
That was the mistake I made, following works now

CREATE OR REPLACE PROCEDURE PTEST(p_in_date date)
IS
v_min date;
v_max1 date;
v_max2 date;

v_b1 boolean:= FALSE;
v_b2 boolean := FALSE;
BEGIN
  
v_b1 := FALSE;
v_b2 := FALSE;
  
dbms_output.put_line('p_in_date: '||p_in_date);
  
select min(x)
into v_min
from test_g
where trunc(x,'MM') = TRUNC(p_in_date,'MM');

IF TRUNC(v_min) = trunc(p_in_date) then
   dbms_output.put_line('yes 1');
  v_b1 := true;
end if;

if v_b1 then
  dbms_output.put_line('yes1');
  
end if;

dbms_output.put_line('v_min: '||v_min);

-- here i want to check if p_in_date is either equal to the earliest entry in test_g for the given month / year
-- or one of the latest two dates for the given month / year, eg: if p_in_date is any month in Nov 2001, check if
-- p_in_date is eihter 11/14/2001 or 11/25/2001



END;


question 2;

I simply have to set v_b2 to true if the given date input param is either equal to the one of the latest two date entries for the given month / year from test_g

Eg: if the input parameter is any date in November 2001, check if that date is equal to either 11/14/2001 or 11/25/2001 (latest two entries for Nov 2001 from test_g) , if so, set v_b2 to true
Re: dates question [message #574675 is a reply to message #574674] Mon, 14 January 2013 16:14 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

CREATE OR REPLACE PROCEDURE PTEST(p_in_date date)
IS
v_min date;
v_max1 date;
v_max2 date;

v_b1 boolean:= FALSE;
v_b2 boolean := FALSE;

f int;
BEGIN
  
v_b1 := FALSE;
v_b2 := FALSE;
  
dbms_output.put_line('p_in_date: '||p_in_date);
  
select min(x),count(decode(x,p_in_date,1))
into v_min,i
from test_g
where trunc(x,'MM') = TRUNC(p_in_date,'MM');

IF TRUNC(v_min) = trunc(p_in_date) then
   dbms_output.put_line('yes 1');
  v_b1 := true;
end if;

if v_b1 then
  dbms_output.put_line('yes1');
  
end if;

dbms_output.put_line('v_min: '||v_min);

-- here i want to check if p_in_date is either equal to the earliest entry in test_g for the given month / year
-- or one of the latest two dates for the given month / year, eg: if p_in_date is any month in Nov 2001, check if
-- p_in_date is eihter 11/14/2001 or 11/25/2001
v_b2:=i>0;


END;

Re: dates question [message #574677 is a reply to message #574674] Mon, 14 January 2013 16:51 Go to previous messageGo to next message
ora1980
Messages: 246
Registered: May 2008
Senior Member
No, I don't want to check if that date is equal to the minimum, it should be equal to
either the maximum (11/25/2001) or the one before that (11/14/2001)....

if so, check v_b2 to be true
Re: dates question [message #574678 is a reply to message #574677] Mon, 14 January 2013 17:11 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Ok, try it:
CREATE OR REPLACE PROCEDURE PTEST(p_in_date date)
IS
  v_min date;
  v_max1 date;
  v_max2 date;

  v_b1 boolean:= FALSE;
  v_b2 boolean := FALSE;

  i int;
BEGIN
  
  v_b1 := FALSE;
  v_b2 := FALSE;
    
  dbms_output.put_line('p_in_date: '||p_in_date);
    
  with t as (
            select x
                  ,row_number()over(order by x desc) rn
            from test_g
            where trunc(x,'MM')=trunc(p_in_date,'MM')
  )
  select min(x)
       , case when p_in_date in (
                       max(decode(rn,1,x))
                      ,max(decode(rn,2,x))
                     ) 
                then 1 
              else 0 end
  into v_min,i
  from t;

  IF TRUNC(v_min) = trunc(p_in_date) then
     dbms_output.put_line('yes 1');
    v_b1 := true;
  end if;

  if v_b1 then
    dbms_output.put_line('yes1');
    
  end if;

  dbms_output.put_line('v_min: '||v_min);

  -- here i want to check if p_in_date is either equal to the earliest entry in test_g for the given month / year
  -- or one of the latest two dates for the given month / year, eg: if p_in_date is any month in Nov 2001, check if
  -- p_in_date is eihter 11/14/2001 or 11/25/2001
  v_b2:=i>0;
  if v_b2 then
    dbms_output.put_line('yes2');
  end if;

END;
Re: dates question [message #574679 is a reply to message #574677] Mon, 14 January 2013 17:30 Go to previous messageGo to next message
ora1980
Messages: 246
Registered: May 2008
Senior Member
this works,

question:

does the following always works no matter what date format the input parameter is ?

trunc(x,'MM')=trunc(p_in_date,'MM')


I think I have to take care that when I call the procedure, the month should be in MM format

I tried to follow the logic but could not understand, it works, but could you please explain in plain english how this works

thanks
Re: dates question [message #574680 is a reply to message #574679] Mon, 14 January 2013 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
when all else fails, Read The Fine Manual

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions209.htm#SQLRF06151
Re: dates question [message #574694 is a reply to message #574679] Tue, 15 January 2013 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about the solution I posted above?

Regards
Michel
Re: dates question [message #574714 is a reply to message #574679] Tue, 15 January 2013 06:38 Go to previous message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
ora1980 wrote on Mon, 14 January 2013 23:30
question:

does the following always works no matter what date format the input parameter is ?

trunc(x,'MM')=trunc(p_in_date,'MM')


The input parameter is of date datatype. Dates don't have formats.
String repesentation of dates have formats.
If you call procedure like this:
exec PTEST(sysdate);

or this:
exec PTEST(to_Date('11-01-2012', 'DD-MM-YYYY');

Or any other method that passes a variable of date datatype then it will work.

If you call it like this:
exec PTEST('11-01-2012');

Then it might not work as it this list case you are passing a string which oracle will convert to a date using your default format mask.

You need to make sure that the value you pass in is converted to a date before hand.
Previous Topic: how to convert varchar2 to number
Next Topic: Native Dynamic Sql
Goto Forum:
  


Current Time: Fri Aug 01 10:15:55 CDT 2014

Total time taken to generate the page: 0.06684 seconds