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

    Unanswered: SQL LOADER Date format error.

    Hi, I am using SQL LOADER to load the oracle database from a text file
    and the text file has the field "2004-01-12 10:16:20.23" to be loaded into oracle.
    Here is my control file;

    LOAD DATA
    INFILE 'C:\SQL\date.txt'
    INTO TABLE TIME_TEST
    TRUNCATE
    FIELDS TERMINATED BY ','
    (
    FILELD1 DATE "TO_DATE(:FILELD1, 'YYYY-MM-DD HH:MIS:ff2')"
    )

    But i always get the message "ORA-01821: date format not recognized" . Any help on this. Thanks in Advance...Uma

  2. #2
    Join Date
    Feb 2004
    Posts
    7
    I think the field "ff" is a new feature in Oracle 9i, the first you should confirm the version of your oracle.
    The second thing is that I notice you use 'YYYY-MM-DD HH:MIS:ff2', try to use 'YYYY-MM-DD HH:MIS.ff2' because your data format is using '.'

  3. #3
    Join Date
    Feb 2004
    Posts
    11
    Originally posted by zs_shine
    I think the field "ff" is a new feature in Oracle 9i, the first you should confirm the version of your oracle.
    The second thing is that I notice you use 'YYYY-MM-DD HH:MIS:ff2', try to use 'YYYY-MM-DD HH:MIS.ff2' because your data format is using '.'
    Thanks for your reply.... the version i am using is oracle 9i and the usage of 'YYYY-MM-DD HH:MIS.ff2' DID NOT solve the problem....

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you are trying to load that into a date field.
    you cannot load hundreths of seconds into a date field.

    change the column-type and you are good to go.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2004
    Posts
    11
    Originally posted by The_Duck
    you are trying to load that into a date field.
    you cannot load hundreths of seconds into a date field.

    change the column-type and you are good to go.

    yes! That worked i changed my date filed to timestamp and changed
    my control file line to
    FILELD1 TIMESTAMP "YYYY-MM-DD HH:MISXFF"
    and it worked.... thanks.....Uma

Posting Permissions

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