| to_date [message #513726] |
Wed, 29 June 2011 03:14  |
 |
x-oracle
Messages: 380
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 #513805 is a reply to message #513726] |
Wed, 29 June 2011 08:28  |
 |
Littlefoot
Messages: 21823
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 |
cookiemonster
Messages: 13961
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 #513905 is a reply to message #513811] |
Thu, 30 June 2011 01:22 |
|
x-oracle
Messages: 380
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 #513918 is a reply to message #513908] |
Thu, 30 June 2011 02:11 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
shaan121 wrote on Thu, 30 June 2011 08:26any 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).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
