ORACLE sqlldr LOAD Utility - Date format

Question

I am loading data into TPC-H tables for Oracle using the sqlldr load functionality. For example, the orders.ctl file has the following.

load data
INFILE 'orders.tbl'
INTO TABLE ORDERS
FIELDS TERMINATED BY '|'
(O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, O_ORDERDATE DATE 
 'YYYY-MM-DD', O_ORDERPRIORITY, O_CLERK, O_SHIPPRIORITY, O_COMMENT)

After loading the data into Orders table, I find that the DATE format is not in 'YYYY-MM-DD' but in the format 'DD-MON-YY'. Why is Oracle not using the format I had mentioned?

Any suggestions?

Thanks!

EDIT : Adding java tag as it involves converting the date value passed as string to a java method to convert it back to Date format. See comment to @Justin Cave answer.

Answer 1

A date column in Oracle does not have a format. A string representing a date has a format. A date is always stored in a binary format that is not particularly human readable. Note that an Oracle date also always contains a time component even if your client doesn't display it.

My guess is that you are saying that when you open up SQL*Plus on a machine where you have done a default US-English install of the Oracle client, connect to the database, and do a

SELECT o_orderdate
  FROM orders

that the date that is displayed is in the format DD-MON-RR. This is because the client needs to convert the date to a string representation in order to display it. If you rely on implicit data type conversion, the client will use your session's NLS_DATE_FORMAT to do the conversion. The default for an English language install in the United States is to have an NLS_DATE_FORMAT of "DD-MON-RR". Each session has its own NLS_DATE_FORMAT because each user potentially wants to display dates in a different format.

If you want to display the string representation of the date in a particular format, you'd want to use explicit data type conversions using the to_char function

SELECT to_char( o_orderdate, 'YYYY-MM-DD' )
  FROM orders