Home » SQL & PL/SQL » SQL & PL/SQL » TO_DATE function returns undesirable format
TO_DATE function returns undesirable format [message #292307] Tue, 08 January 2008 09:02 Go to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi,

I have problem with to_date function which return unexpected format when we 'DD-MM-YYYY' with to_date

Why it gives 0008 when used in first query, given below

SQL> SELECT TO_CHAR(TO_DATE(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO_DATE(SYSDATE,'DD-MM
------------------------------
08-01-0008

SQL> SELECT TO_CHAR(TO_DATE(SYSDATE,'DD-MM-YY'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO_DATE(SYSDATE,'DD-MM
------------------------------
08-01-2008                  

SQL> SELECT TO_CHAR(TO_DATE(SYSDATE,'DD-MM-RR'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO_DATE(SYSDATE,'DD-MM
------------------------------
08-01-2008                  

SQL> SELECT TO_CHAR(TO_DATE(SYSDATE,'DD-MM-RRRR'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO_DATE(SYSDATE,'DD-MM
------------------------------
08-01-2008

Thanks
Trivendra

[Updated on: Tue, 08 January 2008 09:11] by Moderator

Report message to a moderator

Re: TO_DATE function returns undesirable format [message #292308 is a reply to message #292307] Tue, 08 January 2008 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Interesting.

What OS name & version?
Which Oracle version to 4 decimal places?
Re: TO_DATE function returns undesirable format [message #292309 is a reply to message #292307] Tue, 08 January 2008 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SYSDATE is a date, so TO_DATE(SYSDATE...) is meaningless.
Actually, Oracle implicitly applies TO_CHAR on SYSDATE using the default format before applying your TO_DATE.
So the results.

Use SET TRIMOUT ON TRIMSPOOL ON to avoid lines of 255 characters.

Regards
Michel

[Updated on: Tue, 08 January 2008 09:11]

Report message to a moderator

Re: TO_DATE function returns undesirable format [message #292310 is a reply to message #292307] Tue, 08 January 2008 09:11 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Windows XP Version 2002, SP 2
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Re: TO_DATE function returns undesirable format [message #292313 is a reply to message #292307] Tue, 08 January 2008 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
what is returned from:
SQL> SELECT SYSDATE FROM DUAL;
Re: TO_DATE function returns undesirable format [message #292314 is a reply to message #292307] Tue, 08 January 2008 09:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Similar link.

By
Vamsi
Re: TO_DATE function returns undesirable format [message #292317 is a reply to message #292307] Tue, 08 January 2008 09:19 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
SQL> SELECT SYSDATE FROM DUAL;

SYSDATE                                                                         
------------------                                                              
08-JAN-08                                                                       

SQL> SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY') FROM DUAL;

TO_CHAR(SYSDATE,'DD-MM-YYYY')                                                   
------------------------------                                                  
08-01-2008      
Re: TO_DATE function returns undesirable format [message #292319 is a reply to message #292317] Tue, 08 January 2008 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE                                                                         
------------------                                                              
08-JAN-08   


See my answer, it explains why you get 0008 in your queries.

Regards
Michel
Re: TO_DATE function returns undesirable format [message #292320 is a reply to message #292307] Tue, 08 January 2008 09:31 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi Michel,

What if it is not sysdate.

SQL> SELECT TO_CHAR(TO_DATE('01-01-2008','DD-MM-YYYY'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO_DATE('01-01-2008','                                                  
------------------------------                                                  
01-01-2008                                                                      

SQL> SELECT TO_CHAR(TO_DATE(TO_DATE('01-01-2008','DD-MM-YYYY'),'DD-MM-YYYY'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO_DATE(TO_DATE('01-01                                                  
------------------------------                                                  
01-01-0008                                                                      

SQL> SELECT TO_CHAR(TO_DATE(TO_DATE('01-01-2008','DD-MM-YYYY'),'DD-MM-RRRR'),'DD-MM-YYYY') FROM DUAL;

TO_CHAR(TO_DATE(TO_DATE('01-01                                                  
------------------------------                                                  
01-01-2008                                                                      
Re: TO_DATE function returns undesirable format [message #292321 is a reply to message #292307] Tue, 08 January 2008 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
trivendra,
I bet if YOU change your system's default date mask to include a 4 digit year, your mystery will be solved.

Have A Nice Day (HAND)!
Re: TO_DATE function returns undesirable format [message #292324 is a reply to message #292320] Tue, 08 January 2008 09:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
TO_DATE seeks a character string not a date as the first parameter.
Check this.

By
Vamsi
Re: TO_DATE function returns undesirable format [message #292327 is a reply to message #292307] Tue, 08 January 2008 09:44 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks all,

This is very useful details for my standard of coding. This is resolved.

Cheers!!!
Trivendra
Re: TO_DATE function returns undesirable format [message #292329 is a reply to message #292327] Tue, 08 January 2008 09:49 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
One quick point:
Why are you still using to_date on a date:

Quote:

(TO_DATE(TO_DATE('01-01-2008','DD-MM-YYYY'),'DD-MM-YYYY')

Re: TO_DATE function returns undesirable format [message #292339 is a reply to message #292320] Tue, 08 January 2008 10:37 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Hi Michel,

What if it is not sysdate.

Same thing TO_DATE on a date is just silly, logically speaking, doesn't it?
It is like if I'm going to "Ministère de l'Intérieur" (Home Office) ask for being French.

Regards
Michel
Previous Topic: DUPLICATION OF RESULT
Next Topic: Script in PL/SQL language
Goto Forum:
  


Current Time: Fri Dec 09 19:13:09 CST 2016

Total time taken to generate the page: 0.28633 seconds