Insert date into table in other format

[sreedhar Lackka]

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

insert into test values ('Name1,to_date('25-Oct-11','DD-MON-YY'))

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 Vashko]

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:
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY';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.