Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: sqlldr date format mask and null data

    I am trying use sqlldr to load a file in which most of the records for a field are "Mon 24 2003 12:34:000AM"

    The control file defines it like:
    dt date "Mon DD YYYY HH:MiAM",

    I'm getting a
    ORA-01855: AM/A.M. or PM/P.M. required
    error when processing the file for these values.

    I'm using the same file for both Oracle and Sybase, and don't want to change the data if possible.

    I've tried HH:MiSAM, HH:MiS.FF3AM, HH:MiSSSSAM, HH:MiSSAM, HH:MiS.FFAM

    But non of these worked.

    Suggestions?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > "Mon 24 2003 12:34:000AM"
    Is the above timestamp correct?
    AFAIK, Oracle does NOT support seconds be THREE characters,"000".

  3. #3
    Join Date
    Feb 2004
    Posts
    5
    My BAD...
    Its actually trying to do 3 character milliseconds.
    Like:

    12:34:56:000

    I tried HH:MiS:FF3, but it didn't work either. Although Oracle might take FF in functions like TO_CHAR() I don't know if the sqlldr does.

    Any other suggestions?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    From http://download-west.oracle.com/docs...ts2a.htm#47861


    TIMESTAMP Datatype
    The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values. Specify the TIMESTAMP datatype as follows:

    TIMESTAMP [(fractional_seconds_precision)]


    where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6. When you specify TIMESTAMP as a literal, the fractional_seconds_precision value can be any number of digits up to 9, as follows:

    TIMESTAMP'1997-01-31 09:26:50.124'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •