OraFAQ Forum: Client Tools » TO

Home » Other » Client Tools » TO_DATE FORMAT (ORACLE 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TO_DATE FORMAT [message #549801]

Wed, 04 April 2012 00:04

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

Report message to a moderator

Re: TO_DATE FORMAT [message #549802 is a reply to message #549801]

Wed, 04 April 2012 00:06

Littlefoot
Messages: 21826
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>

Report message to a moderator

Re: TO_DATE FORMAT [message #549805 is a reply to message #549802]

Wed, 04 April 2012 00:21

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

Report message to a moderator

Re: TO_DATE FORMAT [message #549808 is a reply to message #549805]

Wed, 04 April 2012 00:51

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

How you are going to use the output ?

1/ in reports ?

Report message to a moderator

Re: TO_DATE FORMAT [message #549809 is a reply to message #549805]

Wed, 04 April 2012 00:52

Littlefoot
Messages: 21826
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.

Report message to a moderator

Re: TO_DATE FORMAT [message #549811 is a reply to message #549805]

Wed, 04 April 2012 00:58

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: TO_DATE FORMAT [message #549830 is a reply to message #549811]

Wed, 04 April 2012 03:12

cookiemonster
Messages: 13975
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.

Report message to a moderator