to_date and separate char date & time fields

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,

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);

Yep, I just now figured that out myself, and have got my problem solved. Thanks for your help!