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
cursor c_time is
select test_date, test_time from dsw_date;
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);
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.
03-27-2001, 06:23 PM
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'.
03-28-2001, 12:38 PM
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
03-28-2001, 05:23 PM
For trimming out the rightmost character from your time field use SUBSTR() function, like: