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  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?
Seems like hibernate doesn’t check that
act_sub_t.sub_dt are of type
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)
CAST(ActSubT.unsubDt - ActSubT.subDt AS int)
should be a good substitute.