CODEWITHSUNDEEP
sqloracle-databaseexternal-tables
Adil
Adil

Reputation: 111

Date Format Options in Oracle External Table

I have created an external table, everything looks good except for one of the date format, it looks like it defaults to MM/DD/YYYY but I want it as YYYYMMDD as it is in the file. The column is DATE_ID. Anyone have any clues?

Thanks!

CREATE TABLE R2SCHD.AAFES_RDF_XTRNL_CAL_HIER
(
  DATE_ID           DATE,
  DATE_DESC         DATE,
  WEEK_ID           VARCHAR2(25 BYTE),
  WEEK_DESC         DATE,
  MNTH_ID           VARCHAR2(25 BYTE),
  MNTH_DESC         VARCHAR2(25 BYTE),
  QRTR_ID           VARCHAR2(25 BYTE),
  QRTR_DESC         VARCHAR2(25 BYTE),
  HALF_ID           VARCHAR2(25 BYTE),
  HALF_DESC         VARCHAR2(25 BYTE),
  YEAR_ID           VARCHAR2(25 BYTE),
  YEAR_DESC         VARCHAR2(25 BYTE),
  DOW_ID            VARCHAR2(25 BYTE),
  DOW_DESC          VARCHAR2(25 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY R2SCHD_STATIC_DATA
     ACCESS PARAMETERS 
     ( RECORDS DELIMITED BY NEWLINE
       NOBADFILE
       NOLOGFILE
       NODISCARDFILE
       FIELDS TERMINATED BY ','
       MISSING FIELD VALUES ARE NULL
      (
       DATE_ID CHAR(8) DATE_FORMAT DATE MASK "YYYYMMDD",
       DATE_DESC CHAR(10) DATE_FORMAT DATE MASK "MM/DD/YYYY",
       WEEK_ID,
       WEEK_DESC CHAR(10) DATE_FORMAT DATE MASK "MM/DD/YYYY",
       MNTH_ID, MNTH_DESC, QRTR_ID, QRTR_DESC, HALF_ID, 
       HALF_DESC, YEAR_ID, YEAR_DESC, DOW_ID, DOW_DESC
      )
     )
    LOCATION (R2SCHD_STATIC_DATA:'rdft_cal_external.dat')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

Upvotes: 1

Views: 6951

Answers (1)

user5683823
user5683823

Reputation:

You created the date_id as DATE data type, which is correct. Dates do not have a "format" - what you see on your screen, with slashes, is dictated by your NLS_DATE_FORMAT setting. What are you using - SQL*Plus? SQL Developer? Toad?

For example in SQL Developer: Tools - Preferences - Database - NLS and you will see (and be able to change) your date format. That is what controls what is shown on your screen - not the format you used for your external table (it won't help you to set it as 'YYYYMMDD' there).

Upvotes: 5

Related Questions