This discussion is archived
8 Replies Latest reply: Dec 25, 2012 1:00 AM by 971918 RSS

Convert clob to varchar to date.

Sudhir_Meru Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points