CODEWITHSUNDEEP
oracle-database
totalitarian
totalitarian

Reputation: 3666

Oracle External Table - How to convert field to a date?

I'm loading some data into oracle using an external table. One of the fields "CREATEDON" is a date but is formatted like this 20151231

How can I convert CREATEDON to a proper oracle date datatype. This is my attempt so far which doesn't work...

  CREATE TABLE "AMS"."DATA" 
   (    "BLANK" VARCHAR2(255 BYTE), 
    "BLANK2" VARCHAR2(255 BYTE), 
    "CLIENT" VARCHAR2(255 BYTE), 
    "MATERIAL" DATE, 
    "CREATEDON" VARCHAR2(255 BYTE)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DIR"
      ACCESS PARAMETERS
      ( records delimited BY '\r\n' 
                skip 1 
      fields terminated BY '|' lrtrim missing field VALUES are NULL 
            )
      LOCATION  ( "DIR":'foo.txt'      )
    )
   REJECT LIMIT UNLIMITED ;

Upvotes: 0

Views: 1130

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12159

You have two options:

  1. Keep as is, and transform in your select statements, such as:

    select ..., to_date(CREATEDON,'YYYYMMDD') CREATEDON

  2. Define the format in the table DDL:

"CREATEDON" VARCHAR2(255 BYTE) DATE_FORMAT DATE MASK "YYYYMMDD"

Upvotes: 2

Related Questions