Home » SQL & PL/SQL » Client Tools » TO_DATE FORMAT (ORACLE 10G)
TO_DATE FORMAT [message #549801] Wed, 04 April 2012 00:04 Go to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hii All,


select to_date('31-12-2012','DD-MM-RRRR') from dual;

OUT PUT : '12/31/2012'

is't possible to show the date format like

'31-12-2012'

without converting to char


Regards

Re: TO_DATE FORMAT [message #549802 is a reply to message #549801] Wed, 04 April 2012 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 21333
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check the example:
SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select to_date('31-12-2012', 'dd-mm-rrrr') from dual;

TO_DATE('3
----------
31.12.2012

SQL> alter session set nls_date_format = 'dd-mm-yyyy';

Session altered.

SQL> select to_date('31-12-2012', 'dd-mm-rrrr') from dual;

TO_DATE('3
----------
31-12-2012

SQL>
Re: TO_DATE FORMAT [message #549805 is a reply to message #549802] Wed, 04 April 2012 00:21 Go to previous messageGo to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi Littlefoot,

Thanx, its working in SQL PLUS.

But when i run this query in Toad. it returns

select to_date('31-12-2012', 'dd-mm-rrrr') from dual;

out put : 12/31/2012



Regards


Re: TO_DATE FORMAT [message #549808 is a reply to message #549805] Wed, 04 April 2012 00:51 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
How you are going to use the output ?

1/ in reports ?
Re: TO_DATE FORMAT [message #549809 is a reply to message #549805] Wed, 04 April 2012 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 21333
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In TOAD, date format is set in "View - TOAD Options" menu, under the "Data Grids - Data" section.
Re: TO_DATE FORMAT [message #549811 is a reply to message #549805] Wed, 04 April 2012 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But when i run this query in Toad


So do not use it, anyway it is b...
...But did you try to use the alter session as Littlefoot showed?

And maybe you can investigate TO_CHAR function.

Regards
Michel
Re: TO_DATE FORMAT [message #549830 is a reply to message #549811] Wed, 04 April 2012 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
Alter session doesn't work in most GUI DB tools, including SQLDeveloper, as they don't implicitly to_char dates like sqlplus does. They treat dates as dates (probably java ones). Consequently they all have an option for date format in their options/preferences menus.
Re: TO_DATE FORMAT [message #549836 is a reply to message #549808] Wed, 04 April 2012 04:09 Go to previous messageGo to next message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi rahulvb,

i'm going to use this in report, which generates in XLS.

regards
Re: TO_DATE FORMAT [message #549837 is a reply to message #549809] Wed, 04 April 2012 04:12 Go to previous message
ORA_KRISH
Messages: 38
Registered: March 2011
Location: chennai
Member
Hi Littlefoot,

its working, thanxs for your time.

Regards

Previous Topic: problem in installation of oracle 10g at window 7 64 bit
Next Topic: connectivity problem to sqlplus
Goto Forum:
  


Current Time: Sat Jan 20 20:00:45 CST 2018

Total time taken to generate the page: 0.22899 seconds