8 Replies Latest reply: Dec 25, 2012 3:00 AM by 971918 RSS

    Convert clob to varchar to date.

    Sudhir_Meru
      Hi,

      Recently few data we uploaded through ETL tools and date type got some how converted and stored in table as clob data type.

      Now my requirement is to convert the clob data type to date.

      Below is the query am using to convert string to date but this is not working.
      SELECT
      TO_DATE(TO_CHAR(DATECREATED),'DD-MM-YYY HH24:MI:SS') DATES
      FROM
      (
      SELECT CAST(DATECREATED AS VARCHAR2(4000)) DATECREATED
      FROM TICKET_DETAILS
      )
      In the above query i am using CAST operator to convert the clob datatype to varchar.

      Below is the format stored in the table I need to get the data in ,'DD-MM-YYY HH24:MI:SS' or ,'DD-MON-YYY HH24:MI:SS' format
      Eg: Mar 7 2007 5:59AM
      Please suggest me.

      Thanks
      Sudhir