File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes Insert date into table in other format Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Insert date into table in other format" Watch "Insert date into table in other format" New topic

Insert date into table in other format

sreedhar Lackka
Ranch Hand

Joined: Jul 05, 2008
Posts: 154
Hi All,

Thanks in advance.

I have created a table test with column names, name varchar2,Date1 Date.

I am trying to insert data into this table like

However when I do select it is simply showing Name1 and 10/25/2011 however I was expecting to display in 25-Oct-11 without applying again Tochar and Todate.

Please guide me.

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

The date value is stored in the database in a binary (not textual) format. Format you used in the to_date() function is not stored, it is not part of the Oracle's DATE datatype. Therefore, when tools like Sqlplus need to print out a DATE value, they need to convert it to text. These conversions are guided by your current NLS (National Language Support) settings.

You can change these settings by altering corresponding session variables - in this case, the NLS_DATE_FORMAT variable, eg:
However, this is only useful in tools like Sqlplus. Do not ever rely on implicit conversions in production code (regardless of the language), as in that case your application gets easily broken as soon as the NLS settings changes.
I agree. Here's the link:
subject: Insert date into table in other format
It's not a secret anymore!