SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert clob to varchar to date.

Sudhir_MeruNov 16 2012 — edited Dec 25 2012

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

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 22 2013

Added on Nov 16 2012

8 comments

2,393 views