Issue in Query

Question

User-182690678 posted

Hi Experts,

Need help in below query

select TO_CHAR(joining_date,'DD-MON-YYYY') from project

above query Throwing invalid number error in oracle. Here joining_date is a varchar field. But if I change this to date datatype then it is working fine.

Need a solution in the query it self without changing the datatype of column. How to do this.

Answer 1

User-660773446 posted

select TO_CHAR(to_date(joining_date,'DD-MON-YYYY'),'DD-MON-YYYY') from project

This should work.

Answer 2

User-2030397957 posted

 Hi

try it and let me know is its working or not . As i dont have oracle installed in my PC i am unable to test is before sending you the solution.

SELECT TO_CHAR(TO_date(joining_date),'DD-MON-YYYY') FROM project

In query convert field to date datatype then give format .

Answer 3

User-182690678 posted

Getting below error

tested both the queries getting this error :"Literal doesn't match format string"  

My requirement is I should not change the datatype for the column, Change only needed in the existing query.

Answer 4

User-660773446 posted

Please verify the joing date is in the format 22-nov-2010

Answer 5

User-182690678 posted

Date format is like this 2011-09-28 00:00:00

Answer 6

User-660773446 posted

select TO_CHAR(to_date(joining_date,'YYYY-MM-DD HH24:MI:SS'),'DD-MON-YYYY') from project

Answer 7

User-182690678 posted
Hi hari_4uin , Many thanks your query resolved my issue.