Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: SQL Loader date format unrecognized

    I'm having trouble loading a file with dates which appear like

    Apr 21 1997 12:00:00:000AM

    I thought the following would work in my control file:

    Code:
    load data
    infile "/nas/transport/shrstatt.bcp" "STR '&@'"
    badfile "/nas/transport/shrstatt.bad"
    discardfile "/nas/transport/shrstatt.dsc"
    into table SHARED.SHRSTATT
    fields terminated by "~"
    trailing nullcols
    (CODE,
     DESCRIPTION,
     START_DATE DATE "Mon dd yyyy hh:mi:ss:ff3AM",
     STOP_DATE DATE "Mon dd yyyy hh:mi:ss:ff3AM",
     ID_OP_ADD,
     ID_OP_UPD,
     DT_TM_ADD DATE "Mon dd yyyy hh:mi:ss:ff3AM",
     DT_TM_UPD DATE "Mon dd yyyy hh:mi:ss:ff3AM")
    because I can run the following SQL statement successfully

    Code:
    select to_char(current_timestamp, 'Mon dd yyyy hh:mi:ss:ff3AM') from dual
    but I keep getting

    Code:
    [oracle@ora4 transport]$ sqlldr control=shrstatt.ctl
    Username:forbesc
    Password:
    
    SQL*Loader: Release 10.1.0.3.0 - Production on Wed Feb 16 10:17:50 2005
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    ORA-01821: date format not recognized
    Here's a sample record
    Code:
    [oracle@ora4 transport]$ more shrstatt.bcp
    00~Unknown             ~Apr 21 1997 12:00:00:000AM~~kordmaft~TORONTDJ~Sep 10 199
    6  4:28:36:570PM~Apr 21 1997  2:29:00:800PM&@
    -Chuck

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Use TIMESTAMP:
    Code:
    ...
    START_DATE TIMESTAMP "Mon dd yyyy hh:mi:ss:ff3AM",
    ...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Right on ... Thanks.
    -cf

Posting Permissions

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