| cursor question [message #81953] |
Thu, 03 April 2003 10:41  |
kim
Messages: 116
Registered: December 2001
|
Senior Member |
|
|
hello,
i am trying to create a cursor that includes a date field. after fetch i would like to assign the date and time from this field to different variables for use later on. how can i go about doing this? so far i have tried:
declare
evnt_name dly_view.evnt_name%type;
start_dt_tm dly_view.start_dt_tm%type;
cursor C1 is
select evnt_name, start_dt_tm
from dly_view
order by start_dt_tm;
begin
open C1;
loop
fetch C1 into evnt_name, start_dt_tm;
start_dt := to_char(start_dt_tm,'DD-MON-RR');
start_tm := to_char(start_dt_tm,'HH12:MI:SS AM');
exit when C1 %notfound or C1 %notfound is null;
end loop;
close C1;
end;
this gives me error ORA-01843 - not a valid month. if someone could help me i would really appreciate it.
thanks!
|
|
|
|
|
|
| Re: cursor question [message #81961 is a reply to message #81953] |
Fri, 04 April 2003 08:25  |
kim
Messages: 116
Registered: December 2001
|
Senior Member |
|
|
thank you, i do not get an error when using this code. however, i am not getting the right data either.
loop
fetch C1 into evnt_name, start_dt_tm;
start_dt := to_date(to_char(start_dt_tm,'DD-MON-RR'),'DD-MON-RR');
start_tm := to_date(to_char(start_dt_tm,'HH12:MI:SS AM','HH12:MI:SS AM');
exit when C1 %notfound or C1 %notfound is null;
end loop;
start_dt gives me the right date, but start_tm is giving me '01-APR-03' as a time. can someone help?
thanks!
|
|
|
|
| Re: cursor question [message #81963 is a reply to message #81961] |
Fri, 04 April 2003 09:53 |
Julie
Messages: 98
Registered: February 2002
|
Member |
|
|
When you put just a time into a datetime column, the date portion will be set to the current date. You can just choose to ignore that portion when you make the format to output the time portion.
What is you objective for separating them? You cannot store a time without a date in the database. I would just leave them combined. You can display the output twice, once date only, once time only.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
