0

I have created a .ctl file to insert some data in oracle 11g. In the input file there is column called LAST_DATE having format (MM/DD/YYYY) and same column is there in oracle with date datatype. But when i try to insert the data its format get changed automatically from (MM/DD/YYYY) or (04/25/2016) to (DD/MM/YYYY) or (25-04-2016). How can I solve this problem. I also define its format in my ctl file TRAILING NULLCOLS (LAST_DATE DATE "MM/DD/YYYY").

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Ashique Sheikh
  • 155
  • 2
  • 4
  • 13

1 Answers1

5

DATE columns do not have a format. The tool you're using to examine the database, however, may have a default format it uses to display DATE columns. For example, in PL/SQL Developer the date format used to display date values is in the Preferences dialog in the NLS Options page of the User Interface section. Best of luck.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • It is now DD-MM-RR so if i changed it to MM/DD/YYYY will the data save as MM/DD/YYYY in db. – Ashique Sheikh May 18 '16 at 12:58
  • 2
    @AshiqueSheikh - read the first sentence again. Date columns do not have a format. Dates are not saved as MM/DD/YYYY or anything else, they have an internal representation, and your client decides how to show that value in a readable format. If you change your client setting then subsequent queries will show the values in that format (if you don't override it with `to_char()`). – Alex Poole May 18 '16 at 13:32