SQL Query - to convert CLOB to DATE

Lazar

Hi,

Help to convert the CLOB column to date . Oracle 12C.

create table test1(id number, comments clob);

create table test2(gid number, id number, app_date date);

insert into test1 values(1,'22-MAY-2021');

insert into test1 values(2,'20-MAY-2021');

insert into test1 values(3,'01-MAY-2021');

insert into test2 values(1,1,'09-MAY-2021');

insert into test2 values(2,2,'20-MAY-2021');

insert into test2 values(3,3,'08-MAY-2021');

SELECT t1.id,T1.COMMENTS,T2.APP_DATE

FROM TEST1 T1,

  TEST2 T2

WHERE T1.ID = T2.ID

AND T1.COMMENTS <> T2.APP_DATE;

ORA-00932: inconsistent datatypes: expected - got CLOB

00932. 00000 - "inconsistent datatypes: expected %s got %s"

*Cause:   

*Action:

Error at Line: 13 Column: 5

Expected Output:-

I'm aware that it can be achieved through a PLSQL block . But all i need here is to convert the CLOB to DATE in the SQL query itself.

Also please note , i dont have access to certain DBMS packages due to some business restrictions.

BEDE

On Oracle 19 the following select works OK:

select t.*, to_date(comments default null on conversion error,'dd-mon-yyyy') x_date

from test1 t

;

In Oracle 12c you don't have the facility of giving a default value on conversion error, so, in case you don't have the proper date format, the select below will fail:

select t.*, to_date(comments,'dd-mon-yyyy') x_date

from test1 t

;

Just insert the select using this to_date and it should work if you have the proper date format in each of the rows. But that's a very iffish question... Are you sure each row will have the proper date in that CLOB column. If not, then the only thing I could think of would be to try to insert row by row:

declare

v_date date;

begin

for r in (select t.* from test 1) loop

begin

v_date:=to_date(r.comments,'dd-mon-yyyy');

exception

when others then

--- how to handle that error if not by saying it is null date???

v_date:=null;

end;

insert into test3 (id, app_date) values(r.id,v_date);

end loop;

commit;

end;

And then use the test3 table instead of the one having the CLOB.

BluShadow

https://community.oracle.com/tech/developers/discussion/4487950/sql-query-to-convert-clob-to-date

The first question should be... Why have you got dates stored in a CLOB in the first place?

There's clearly been some issue with the design somewhere. Best solution is to fix that at source and ensure the dates are stored correctly in a DATE datatype in the first place.

User_H3J7U

To avoid conversion errors - to_char. But to_date is more flexible for date variations.

BEDE

https://community.oracle.com/tech/developers/discussion/comment/16805813#Comment_16805813

Good point. Yet in a CLOB one may have anything, including date with a different date format...

So, like Blue Shadow said, best fix the design issue so as to avoid having such a thing become every ay practice. If it's to be a one time only data processing, then it is admissible.

And one more thing for the beginners: never ever rely on implicit conversions.

Then, of course to_char for date or number will always work, but sometimes it would be it is better to convert to number or possibly date if there is an index on that column.

Frank Kulash

HI, @Lazar

create table test2(gid number, id number, app_date date);

...

insert into test2 values(1,1,'09-MAY-2021');

Don't try to insert a string value (such as '09-MAY-2021') into a DATE column (such as app_date). Use TO_DATE or DATE literals instead. Trying to insert a string into a DATE column is an example of an implicit conversion which Bede warned about. It might not cause an error today - it might even do what you want - but the exact same code may not work tomorrrow.

BEDE

https://community.oracle.com/tech/developers/discussion/comment/16805816#Comment_16805816

That depends on the setting of nls_date_format. That may vary from one database to another and it may also very from one session to another. You should not rely that everyone has the same nls_date_format. An alternative to using format masks is alter session set nls_date_format='dd-mon-yyyy' before running that code.

User_44U40

Following sql works fine I tested it and it gave the output

SELECT t1.id,T1.COMMENTS,T2.APP_DATE

FROM TEST1 T1,

 TEST2 T2

WHERE T1.ID = T2.ID

AND to_date(T1.COMMENTS) <> T2.APP_DATE;

Result Set 4

IDCOMMENTSAPP_DATE122-MAY-202109-MAY-21301-MAY-202108-MAY-21Download CSV

2 rows selected.

User_H3J7U

@User_44U40

Following sql works fine I tested it and it gave the output

to_date has already been mentioned with cautions that you have ignored.