Oracle TO_DATE function parallel in Mysql which should support both DB oracle and mysql Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Oracle TO_DATE function parallel in Mysql which should support both DB oracle and mysql without wasting too much if your time.

The question is published on by Tutorial Guruji team.

Initially, I was facing one weird problem, below query was running fine in Oracle SQL developer.

select * from sbill.act_sub_t where (act_sub_t.unsub_dt - act_sub_t.sub_dt) < 100;

but I was facing below issue in java Parameter value [100] did not match expected type[java.time.LocalDateTime (n/a)]

Below was the java code,

Query query1 = entityManager.createQuery("select * from ActSubT where (ActSubT.unsub_dt - ActSubT.sub_dt)<:days").setParameter("days", 100);

So to fix that problem I had used TO_DATE function in my java query and I was able to run in java

select * from sbill.act_sub_t where (TO_DATE(ActSubT.unSubDt, 'DD-MM-YYYY') - TO_DATE(ActSubT.actualUnsubDt, 'DD-MM-YYYY')) < 100;

But actual problem is that my application supports both Oracle and MySQL Db and that TO_DATE function does not support in MYSQL so this query will not run when this application will run on MySQL.

So do we have any common parallel function of TO_DATE which supports both Oracle and MySQL?

Answer

Seems like hibernate doesn’t check that act_sub_t.unsub_dt and act_sub_t.sub_dt are of type DATE.

As TO_DATE is Oracle only, you can use the CAST-function which is common in your databases:

CAST(ActSubT.unsubDt AS date) - CAST(ActSubT.subDt AS date) 

or

CAST(ActSubT.unsubDt - ActSubT.subDt AS int) 

should be a good substitute.

We are here to answer your question about Oracle TO_DATE function parallel in Mysql which should support both DB oracle and mysql - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji