-
Expert Member
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)
-
Expert Member
Re: Date Conversion Problem
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;
-
Junior Member
Re: Date Conversion Problem
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.
-
Junior Member
Re: Date Conversion Problem
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.
-
Junior Member
Re: Date Conversion Problem
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules