Answered by:
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.
Wednesday, November 24, 2010 4:43 AM
Answers
-
User-660773446 posted
select TO_CHAR(to_date(joining_date,'YYYY-MM-DD HH24:MI:SS'),'DD-MON-YYYY') from project
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 24, 2010 8:46 AM
All replies
-
User-660773446 posted
select TO_CHAR(to_date(joining_date,'DD-MON-YYYY'),'DD-MON-YYYY') from project
This should work.
Wednesday, November 24, 2010 5:28 AM -
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 .
Wednesday, November 24, 2010 5:32 AM -
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.
Wednesday, November 24, 2010 6:00 AM -
User-660773446 posted
Please verify the joing date is in the format 22-nov-2010
Wednesday, November 24, 2010 6:19 AM -
User-182690678 posted
Date format is like this 2011-09-28 00:00:00
Wednesday, November 24, 2010 8:35 AM -
User-660773446 posted
select TO_CHAR(to_date(joining_date,'YYYY-MM-DD HH24:MI:SS'),'DD-MON-YYYY') from project
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 24, 2010 8:46 AM -
User-182690678 posted
Hi hari_4uin , Many thanks your query resolved my issue.Thursday, November 25, 2010 5:59 AM