This discussion is archived
4 Replies Latest reply: Feb 7, 2007 11:08 AM by 529670 RSS

Syntax for milliseconds in DATE field in an external table

529670 Newbie
Currently Being Moderated
I can't figure it out.

I've got one field that is formatted down to the second and this works:
dtg_update char date_format date mask "MM/DD/YY HH24:MI:SS"

But I've got another field that is down to the millisecond: 2/7/2007 12:01:04.891

Using:
change_date char date_format date mask "MM/DD/YY HH24:MI:SS.FF3"
or various combinations thereof doesn't seem to work. When I select from the external table I get date conversion errors.

What's the appropriate format for milliseconds?

Thanks.
  • 1. Re: Syntax for milliseconds in DATE field in an external table
    MichaelS Guru
    Currently Being Moderated
    Don't know if there is an easier way:
    SQL> SELECT TO_CHAR (SYSTIMESTAMP, 'dd.mm.rrrr hh24:mi:ss.ff') a,
           TO_CHAR (CAST (SYSTIMESTAMP AS TIMESTAMP ( 3 )),
                    'dd.mm.rrrr hh24:mi:ss.ff'
                   ) b
      FROM DUAL
    
    A                             B                            
    ----------------------------- -----------------------------
    07.02.2007 18:30:46.523728    07.02.2007 18:30:46.524     
  • 2. Re: Syntax for milliseconds in DATE field in an external table
    529670 Newbie
    Currently Being Moderated
    Michaels,

    Thanks for the reply, but this is in an external table creation. eg:

    CREATE TABLE TEST_TABLE(
    COL1 CHAR(8),
    DATE_1 TIMESTAMP,
    DATE_2 TIMESTAMP)
    ORGANIZATION EXTERNAL (
    type oracle_loader
    default directory EXT_DIRECTORY
    access parameters (
    records delimited by newline
    FIELDS TERMINATED BY 0X'09'
    missing field values are null
    (COL1
    , DATE_1 char date_format date mask "MM/DD/YY HH24:MI:SS"
    , DATE_2 char date_format date mask "MM/DD/YY HH24:MI:SS.ff"
    )
    location ('testfile.txt')
    )
    reject limit unlimited;

    The above external table compiles, but when you SELECT from it you get an invalid date mask error for the DATE_2 column.

    Using no mask or the same mask as for DATE_1 I simply get no rows returned and inside the .log file I see errors for the date conversion since it does not match the default date mask.

    The code you posted is for a standard SELECT which does not seem to play well inside of an external table syntax. Or if it does, I'm missing how to incorporate it correctly.
  • 3. Re: Syntax for milliseconds in DATE field in an external table
    MichaelS Guru
    Currently Being Moderated
    Try replacing the line
    <p>
    , DATE_2 char date_format date mask "MM/DD/YY HH24:MI:SS.ff"
    <p>
    with
    <p>
    , DATE_2 char date_format TIMESTAMP mask "MM/DD/YY HH24:MI:SS.ff3"
  • 4. Re: Syntax for milliseconds in DATE field in an external table
    529670 Newbie
    Currently Being Moderated
    aaaHA!

    There were two problems. One was your use of TIMESTAMP, the other was the fact that the year was actually YYYY. I had missed that.

    Thank you very much, that saved me quite a bit of hair pulling.