Home » SQL & PL/SQL » SQL & PL/SQL » Date Format
Date Format [message #187912] Wed, 16 August 2006 06:12 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a column descr10 which is of datatype VARCHAR2.
The value stored is like:
2006-08-26-00:00
I have to display it in the format MM/DD.
I tried using the following but its not working.....

SQL> select TO_CHAR(to_date(substr(descr10,1,10)),'MM/DD') from si_info_rpting;
ERROR:
ORA-01847: day of month must be between 1 and last day of month


Please advice.
Re: Date Format [message #187918 is a reply to message #187912] Wed, 16 August 2006 06:21 Go to previous messageGo to next message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi,

You missed out the format of the date, chk out the below query...


SELECT TO_CHAR (TO_DATE (SUBSTR ('2006-08-26-00:00', 1, 10), 'YYYY-MM-DD'),
                'MM/DD'
               ) AS RESULT
  FROM DUAL



Thanks
Vinod V
Re: Date Format [message #187925 is a reply to message #187918] Wed, 16 August 2006 06:38 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Vino, It worked.

Re: Date Format [message #187931 is a reply to message #187912] Wed, 16 August 2006 07:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just one more example of why you should store dates in DATE columns.
Previous Topic: How to Use Cross Tab query
Next Topic: Filter on a specific error
Goto Forum:
  


Current Time: Fri Dec 09 13:58:29 CST 2016

Total time taken to generate the page: 0.19433 seconds