[Stackoverflow] [Progress OpenEdge ABL] How to convert date to datetime using JpaRepository in an OpenEdge/Progress database

[Jesser]

I have 2 fields that make up a date with time in a Progress database. One is a date type, "inv-date", and the other is an integer, "inv-time". I want to get the max date with time in a specific query in my Java app.

I am able to execute this query in DBeaver:

SELECT MAX({ fn CONVERT("inv-date", SQL_TIMESTAMP) } + ("inv-time" * 1000))
FROM   pub."inv"
WHERE  "acct-id" = 120324 AND "inv-type" IN (1, 2)

It will return a date with the time added to it. When I execute it, I see that it actually does this:

SELECT MAX(odbc_convert("inv-date", SQL_TIMESTAMP) + ("inv-time" * 1000))

I have tried to use both of those in a @Query on my Java interface, like this:

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {

@Query(
   value = "SELECT MAX({ fn CONVERT(\"inv-date\", SQL_TIMESTAMP) } + (\"inv-time\" * 1000)) " +
   " FROM   pub.\"inv\" " +
   " WHERE  \"acct-id\" = ?1 AND \"inv-type\" IN (1, 2)",
   nativeQuery = true
)
Optional<Date> getLastPaymentInvoiceDate(Long aAccountId);
// also tried just getting Object
}

It errors on running this query, can't extract Result set. I tried also using

SELECT MAX(odbc_convert(\"inv-date\", SQL_TIMESTAMP) + (\"inv-time\") * 1000))

in the @Query and that gave the same error.

What can I do to convert the date into a datetime so I can do this operation in my Java app?

Continue reading...