Home » SQL & PL/SQL » Client Tools » to_date (windows 7, oracle 10.2)
to_date [message #513726] Wed, 29 June 2011 03:14 Go to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
when i run this systax

SQL> select TO_DATE(sysdate,'dd-mon-yyyy') from dual;

i got this output


TO_DATE(S
---------
29-JUN-11

but actuly i want 29-jun-2011 as in my output

but when i run this systax

SQL> select TO_char(sysdate,'dd-mon-yyyy') from dual;

i got this perfect output this

TO_CHAR(SYS
-----------
29-jun-2011

but why i cannot get this output in to_date systax

[Merged and relocated by LF]

[Updated on: Wed, 29 June 2011 08:30] by Moderator

Report message to a moderator

Re: to_date [message #513730 is a reply to message #513726] Wed, 29 June 2011 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why i cannot get this output in to_date systax

Because TO_DATE applies to a STRING not to a DATE.
Trying to convert a date into a date using TO_DATE isn't a bit silly?

Regards
Michel
Re: to_date [message #513805 is a reply to message #513726] Wed, 29 June 2011 08:28 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SYSDATE is a function that returns DATE datatype. So why would you want to "convert" value that already IS a DATE into another DATE datatype? That's silly. I believe you wanted to use TO_CHAR instead, which would then display DATE value in any (valid) format you choose (such as DD-MON-YYYY).

[Updated on: Wed, 29 June 2011 08:32]

Report message to a moderator

Re: to_date [message #513811 is a reply to message #513805] Wed, 29 June 2011 08:46 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
And let's be clear. Becuase to_date doesn't except a date parameter, oracle has to do an implicit conversion. So this:
select TO_DATE(sysdate,'dd-mon-yyyy') from dual

Is effectively this:
select TO_DATE(to_char(sysdate, '<default date format>'),'dd-mon-yyyy') from dual

In PL/SQL. However sqlplus also does an implict conversion. So in sqlplus it becomes:
select to_char(TO_DATE(to_char(sysdate, '<default date format>'),'dd-mon-yyyy'), '<default date format>') from dual
Re: to_date [message #513900 is a reply to message #513811] Thu, 30 June 2011 01:01 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
yes but my question is

when i run this systax on sqlplus i got this result

SQL> select TO_DATE(sysdate,'dd-mon-yyyy') from dual
2 /

TO_DATE(S
---------
30-JUN-11


and this same syntax i run this on toad i got this result

select to_date(sysdate, 'dd-month-yyyy') from dual;

6/30/0011

why i got this different result

and i actuly want date in this format

30/06/2011 so which sytax i have to run

Re: to_date [message #513903 is a reply to message #513900] Thu, 30 June 2011 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why i got this different result

Different default format.

Regards
Michel
Re: to_date [message #513905 is a reply to message #513811] Thu, 30 June 2011 01:22 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
also the result is diffrent in this systax also

SQL> select TO_DATE(to_char(sysdate, 'dd-mon-yy'),'dd-mon-yyyy')from dual;

TO_DATE(T
---------
30-JUN-11


and on toad

TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YY'),'DD-MON-YYYY')
6/30/0011

but i got result same when i run this systax on sqlplus and toad also

SQL> select to_char(TO_DATE(to_char(sysdate, 'dd-mon-yy'),'dd-mon-yyyy'),'dd-mon
-yy') from dual;

TO_CHAR(T
---------
30-jun-11

SQL>

and on toad also

select to_char(TO_DATE(to_char(sysdate, 'dd-mon-yy'),'dd-mon-yyyy'),'dd-mon-yy') from dual;



TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YY'),'DD-MON-YYYY'),'DD-MON-YY')
30-jun-11

Re: to_date [message #513908 is a reply to message #513903] Thu, 30 June 2011 01:26 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
so michel what can i do

any special setting can i do in toad so i can get same result in both

and i want date format in this format

30/06/2011 in both sqlplus and in toad also michel
Re: to_date [message #513912 is a reply to message #513908] Thu, 30 June 2011 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so michel what can i do

ALWAYS use a format with TO_DATE.
For the most outer TO_CHAR:
* if you want a constant output, give a format to TO_CHAR
* if you want to let the user get the output as he wants omit the outer TO_CHAR

Quote:
any special setting can i do in toad so i can get same result in both

Do NOT use TOAD you will avoid many problems.
If you want to get a report (the result of a query), use SQL*Plus.

Regards
Michel
Re: to_date [message #513918 is a reply to message #513908] Thu, 30 June 2011 02:11 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
shaan121 wrote on Thu, 30 June 2011 08:26
any special setting can i do in toad so i can get same result in both

This does not seem to be related with SQL nor PL/SQL, just TOAD. Maybe asking this question in forum Client Tool could lead to better response (as a few people visiting this forum do).

Or, much better, use TOAD's own online help (F1) and search for NLS_DATE_FORMAT (the default format which cookiemonster explains).

Also, searching the internet may give valuable results:
http://asktoad.com/DWiki/doku.php/faq/answers/editor?s=nls
Chapter "What's the deal with dates?" may interest you.

However, firstly, try to understand what cookiemonster tried to explain. Then, you will not try to make meaningless things based on wrong assumptions (as you did in your first post).
Re: to_date [message #513925 is a reply to message #513918] Thu, 30 June 2011 04:12 Go to previous message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
thanks flyboy this site realy helpfull me

and i already asked this question there in forms also but someone has delete my this question from there so.

[Updated on: Thu, 30 June 2011 04:13]

Report message to a moderator

Previous Topic: apps design using pl/sql
Next Topic: sqlplus
Goto Forum:
  


Current Time: Sun Sep 21 03:50:39 CDT 2014

Total time taken to generate the page: 0.11805 seconds