1

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?

Dushyant Tankariya
  • 1,432
  • 3
  • 11
  • 17
Harish Bagora
  • 686
  • 1
  • 9
  • 26
  • `CAST(ActSubT.unSubDt AS DATE)`should work for both databases... – Radagast81 Aug 08 '19 at 12:28
  • What is the datatype of the columns? – Salman A Aug 08 '19 at 12:30
  • @Radagast81 i just tried that solution this is my query select ActSubT.id,ActSubT.actId from ActSubT ActSubT where (CAST(ActSubT.unsubDt AS DATE) - CAST(ActSubT.subDt AS DATE) ) < 10 but now i am getting below exception java.lang.IllegalArgumentException: org.hibernate.QueryException: Could not resolve requested type for CAST : DATE [select ActSubT.id,ActSubT.actId from com.sbill.app.domain.ActSubT ActSubT where (CAST(ActSubT.unsubDt AS DATE) - CAST(ActSubT.subDt AS DATE) ) < 10] – Harish Bagora Aug 08 '19 at 12:52
  • @SalmanA Java side my data type name is LocalDateTime and in oracle data type is DATE – Harish Bagora Aug 08 '19 at 12:52
  • Hmm, that is a problem of hibernate, does `cast(act_sub_t.unsub_dt - act_sub_t.sub_dt as int)` work? – Radagast81 Aug 08 '19 at 12:57
  • @Radagast81 Thanks for your support :) In my old query i changed 'Date' capital to small case 'date' then it works . and this new solution also works. thanks again. – Harish Bagora Aug 08 '19 at 13:02
  • @Radagast81 As of now i did't test it with mysql hope it will work , i will test it. – Harish Bagora Aug 08 '19 at 13:04
  • Posted my suggestions as answer, so you can accept it, if it works for mysql as well. – Radagast81 Aug 08 '19 at 13:10

1 Answers1

1

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.

Radagast81
  • 2,921
  • 1
  • 7
  • 21
  • I have one more query it would be good if you can help on that.. select TO_DATE(e.created_dt, 'dd-mm-yy'),sum(CURRENT_BAL) from sbill.act_resource_t e group by TO_DATE(e.created_dt, 'dd-mm-yy'); here i have used TO_DATE because i wanted to group by just on date without time. here what can i use which can run on both oracle and mysql ? – Harish Bagora Aug 08 '19 at 13:33
  • I doubt that there is a solution that works for both databases Oracle `TRUNC(e.created_dt)` vs. MySQL `DATE(e.created_dt)`... – Radagast81 Aug 08 '19 at 13:42
  • whenever i need to calculate diff b/w two date i do date2 - date1 so will it work with mysql too ? – Harish Bagora Aug 09 '19 at 07:43
  • It should, i don't have the settings to test it. But as you tested it once, i'm sure it will work in all cases. – Radagast81 Aug 09 '19 at 08:06