• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Insert date into table in other format

 
sreedhar Lackka
Ranch Hand
Posts: 154
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Regards,
Sree
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic