1

I have a table in Oracle which contains the column DATUM of type Date. After running "jooq-codegen:generate", a Java class was created where the field DATUM got the type LocalDate.

Now I would like to have a difference between 01.01.2022 and DATUM in JOOQ. In Oracle, it would simply be

TO_DATE('01.01.2022') - DATUM

However I can't manage to translate that into JOOQ.

With minus it does not accept the data type. The dateDiff function does not work neither because DATUM in Java became LocalDate and it has to be Date. How can I transform the type within the JOOQ-Statement? Or any other solutions?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Volokh
  • 380
  • 3
  • 16
  • Does this answer your question? [Calculate days between two Dates in Java 8](https://stackoverflow.com/questions/27005861/calculate-days-between-two-dates-in-java-8) – astentx Jul 30 '21 at 21:15
  • @astentx That is not a duplicate of this question. jOOQ is a library to generate SQL queries, the question you link is about getting a difference in Java itself. – Mark Rotteveel Jul 31 '21 at 09:58

1 Answers1

2

All the functions your are looking for are on the DSL class

DSL.localDateDiff(DSL.localDate("01.01.2022"), TABLE.DATUM);

Please checkout the API Doc: https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/DSL.html

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • As I have mentioned TABLE.DATUM is of type LocalDate and DSL.dateDiff expects an object of type Date. – Volokh Jul 31 '21 at 22:02
  • I've updated my answer. Please also check the DSL class it has many helper functions. – Simon Martinelli Aug 02 '21 at 07:20
  • Thanks, localDateDiff was the right thing to do. In my case localDate("01.01.2022") however returned null. I had to solve that differently. However it would still be interesting how to do it with the function, maybe it is possible to specify the format or stomething like that to avoid null? – Volokh Aug 02 '21 at 16:40
  • 1
    @Volokh: Use the ISO 8601 format: `localDate("2022-01-01")` – Lukas Eder Aug 02 '21 at 19:43