3

I am facing weird problem in one query

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

Using this query i am getting result in oracle sql developer tool but facing below issue with java.

 Parameter value [100] did not match expected type 
 [java.time.LocalDateTime (n/a)]

Below is the java code

  Query query1 = entityManager.createQuery("select * from ActSubT 
  where (ActSubT.unsub_dt - ActSubT.sub_dt) <:days").setParameter("days", 
   100);
Harish Bagora
  • 686
  • 1
  • 9
  • 26

1 Answers1

0

Can you try with TO_DATE()

select * from sbill.act_sub_t 
where (TO_DATE(act_sub_t.unsub_dt, 'YYYY-MM-DD') - TO_DATE(act_sub_t.sub_dt, 'YYYY-MM-DD')) < 100;
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
  • Yes i have tried with toDate , its working now but earlier when i was running this query from sql developer then 10 records were coming but now with toDate 8 records are coming .. i am checking why this happening. – Harish Bagora Jun 07 '19 at 11:58
  • As of now my application is using oracle db but later on it will use mysql as well. so after that this solution will not work as mysql doesn't support "TO_DATE" function. Any solution with mysql ? – Harish Bagora Jun 10 '19 at 08:43
  • @HarishBagora The question asked for Oracle, so if the answer is helped to solve your issue, you can [accept the answer](https://meta.stackexchange.com/a/5235/312043). For MySQL related, you can ask a new question with the possible query you have and what you tried for MySQL. – Arulkumar Jun 10 '19 at 09:01
  • okay... accepted , i will ask new question for that thanks :) – Harish Bagora Jun 10 '19 at 09:06
  • For MySQL, can you try with [DATEDIFF()](https://www.w3resource.com/mysql/date-and-time-functions/mysql-datediff-function.php). So something like `select * from sbill.act_sub_t where DATEDIFF(act_sub_t.unsub_dt, act_sub_t.sub_dt) < 100;` – Arulkumar Jun 10 '19 at 09:10
  • Do we have something like that which is common for both oracle and mysql ? As of now my application support oracle DB but in future if any other customer wants MySQL support then i will migrate it in to the MySQL . so i want such a code in my application which support both the DB. – Harish Bagora Jun 10 '19 at 10:07
  • @HarishBagora I'm not aware such a method available to support Oracle and MySQL. – Arulkumar Jun 10 '19 at 10:17