to_date and separate char date & time fields

[welchdor]

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.

[jmodic]

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,

[welchdor]

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

[jmodic]

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

[welchdor]

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