Home » SQL & PL/SQL » SQL & PL/SQL » Date data type (Oracle 9i)
Date data type [message #322765] Mon, 26 May 2008 03:21 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have a question. Please help me out.

What is the default "display date format" and default internal "accepting date format"? How to change the default display and accepting format. In my system, the default display date format in sql * plus is dd-mon-yy, where as in toad it is mm/dd/yyy HH:MI:SS AM

Can we change these two default display date format.I know I can use to_char. But without any explicit conversion functions during the selection.

insert into mine_date values(to_date('22/03/1984','dd/mm/yyyy'))

What is the internal date format of the date(22/03/1984)that has been changed by to_date function.
Re: Date data type [message #322769 is a reply to message #322765] Mon, 26 May 2008 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You MUST always put the format in TO_CHAR/TO_DATE but maybe for display (returned values from SELECT).

Default format is given by NLS_DATE_FORMAT parameter family.
See V$$NLS_PARAMETERS view.

Regards
Michel
Re: Date data type [message #322783 is a reply to message #322769] Mon, 26 May 2008 04:14 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Thanks Michel Cadot.

Your answer is right, I tried

" alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS' "

But these changes are only for session. Cant we make permanent.

What about the display format?? (any body pls.)
and

" insert into mine_date values(to_date('22/03/1984','dd/mm/yyyy'))"

Does the above commnad internally converts the date format into 'DD-MON-YY' and stores in the data base in the same format??
Re: Date data type [message #322787 is a reply to message #322783] Mon, 26 May 2008 04:24 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Cant we make permanent.

Each client have to put it in its environment registry or variable.

Quote:
Does the above commnad internally converts the date format into 'DD-MON-YY' and stores in the data base in the same format??

It converts your format ('dd/mm/yyyy') into internal one which you don't have no need to know what it is.

Regards
Michel
Previous Topic: help with cursor
Next Topic: Selec not null colmn only
Goto Forum:
  


Current Time: Sat Dec 10 01:32:43 CST 2016

Total time taken to generate the page: 0.10378 seconds