Thread: Date Conversion Problem

In a table indate and outdate are of varchar2 type. It stores data in the the format DD-MON-YYYY HH:MI (03-Apr-2006 16:48). While generating the report I need to display the date in the format DD/MM/YYYY. For that I have given the following command

select to_date(indate,'DD/MM/YYYY') , to_date(outdate,'DD/MM/YYYY') from cmswhitesugarweighment

It is showing errormessage

ORA-01858: a non-numeric character was found where a numeric was expected

Could anybody please suggest how to display date in DD/MM/YYYY format?

My table structure is

VEHICLENO VARCHAR2(50),
PRODUCT_CODE VARCHAR2(20),
PRODUCT_NAME VARCHAR2(50) ,
DELIVERYNOTE_NO VARCHAR2(50),
NO_OF_BAGS NUMBER,
DDNO VARCHAR2(70),
GROSSWEIGHT NUMBER,
TAREWEIGHT NUMBER,
NETWEIGHT NUMBER,
WEIGHNO VARCHAR2(50),
WEIGHMENT_DATE DATE,
INDATE VARCHAR2(50),
OUTDATE VARCHAR2(50),
SHIFTNO VARCHAR2(50),
CREATEDDATE DATE,
CREATEDBY VARCHAR2(50),
PARTYCODE VARCHAR2(50),
PARTYNAME VARCHAR2(50),
SEASONCODE VARCHAR2(10)

Try This.......
select SUBSTR(INDATE,1,2)||'/'||DECODE(SUBSTR(INDATE,4,3),'JAN','01','FEB','02',
'MAR','03','APR','04','MAY','05','JUN','06','JUL','07','AUG','08','SEP','09',
'OCT','10','NOV','11','DEC','12')||'/'||SUBSTR(INDATE,8,4) from cmswhitesugarweighment;

Hi,

1. you have to convert the varchar2 to date using to_date() function.
2.you have convert the date into required format ('dd/mm/yyyy') using to_char() function.
3.you have to convert the character string to date by using to_date() function again.

Please find the below sample:

to_date(to_char(to_date(indate,'DD-MON-YYYY HH:MI'),'dd/mm/yyyy'),'dd/mm/yyyy')

Note: The format argument to the to_date function specifies that the given string argument is in the given format.Eg: to_date('01-01-2007','dd-mm-yyyy') specifies that the string '01-01-2007' is in 'dd-mm-yyyy' format. It does not convert the string the given format.

If you just want to display it as a character you don't need to do the final date conversion. This should do the trick:

select to_char(to_date(indate,'DD/MM/YYYY HH:MI'), 'DD/MM/YYYY')
to_char(to_date(outdate,'DD/MM/YYYY HH:MI'), 'DD/MM/YYYY')
from cmswhitesugarweighment;

Cheers,
Stu.

i think this statement was work properly

select to_char(to_date(indate,'dd-mon-yyyy HH24:MI'),'dd/mm/yyyy') from tem1;

thank you