-
I need to concatonate an varchar2(8) date field, format of YYYYMMDD to a varchar2(7) time field, format of HHMMSSS and make a normal Oracle DATE field out of it.
I have tried the following procedure:
create or replace procedure my_dates
as
v_date_char varchar2(16);
v_new_date DATE;
cursor c_time is
select test_date, test_time from dsw_date;
begin
for t_rec in c_time LOOP
EXIT WHEN c_time%NOTFOUND;
v_date_char := t_rec.test_date || ' ' || t_rec.test_time;
v_new_date := to_date(v_date_char, 'YYYYMMDD HHMMSSS');
dbms_output.put_line(v_date_char || ' ' || v_new_date);
end loop;
end;
But get this error on execution:
ERROR at line 1:
ORA-01810: format code appears twice
ORA-06512: at "MY_DATES", line 11
ORA-06512: at line 1
Can someone help me with TO_DATE to accomplish this? Thanks in advance.
-
You are using format string 'MM' both for months and minutes! Format string for minutes is 'MI'. Also, what do your source time field 'HHMMSSS' contains in a 'SSS' part? There are only 60 seconds in a minute, sou you should have only two 'S'. And as you dont have AM/PM in a source time field, I belive you should use 'HH24'. So your complete format string should be something like 'YYYYMMDD HH24MISS'.
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks to your help, I am getting closer. The "MI" vs. the "MM" was a big part of the problem. The time field is coming from a DB2 time field, and all I know is the format is CHAR(7) formatted as HHMMSSS. I have seen other code examples where an Oracle date field is being split out into the DB2 date and time fields, and they have concatonated a '0' on to the Oracle time to satisfy the DB2 requirement, so I guess I need to truncate that when converting to an Oracle date. How could I truncate the right-most character of that field, no matter what it contained?
Thanks for your help
-
For trimming out the rightmost character from your time field use SUBSTR() function, like:
v_date_char := t_rec.test_date || ' ' || SUBSTR(t_rec.test_time,1,6);
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Yep, I just now figured that out myself, and have got my problem solved. Thanks for your help!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|