locked
Issue in Query RRS feed

  • 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