to_date and separate char date & time fields
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: to_date and separate char date & time fields

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Jan 2001
    Posts
    63
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Jan 2001
    Posts
    63
    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
  •  



Click Here to Expand Forum to Full Width