JPA and Sysdate issue - forcefully accepting Double data types

Question

I am using Spring Data JPA and developed below query which will dynamically take the day values and fetch data, but looks like its arguable looking for Double data type. Any reason why its taking double?

@Query("SELECT new com.XXX.SomeDTODto(p.visitDate, ..........) "
        + "FROM PatientData p "
        + "INNER JOIN ................. "
        + "INNER JOIN ................."
        + "INNER JOIN .................. "
        + "INNER ..... "
        + "WHERE (TRUNC (p.visitDate) >= TRUNC (SYSDATE + :startDay) AND TRUNC (p.visitDate) <= TRUNC (SYSDATE + :endDay))")
Page<SomeDTODto> findByvisitDateBetweenDays(@Param("startDay") Double startDay,
                                                   @Param("endDay") Double endDay, Pageable pageable);

I am getting below, functional test cases runs against H2 DB and failing

here TRUNC(patient0_.patient_dt)>=TRUNC(SYSDATE+?) and TRUNC(patient0_.patient_dt)<=TRUNC(SYSDATE+?) order by patient0_.patient_dt asc limit ? [50004-196]

Multicolor

Answer 1

You can use below, this works fine for me, also Integer is getting casted to Double

"WHERE (TRUNC (p.visitDate) >= TRUNC (SYSDATE + CAST(:startDay AS double) + 0) AND TRUNC (p.visitDate) <= TRUNC (SYSDATE + CAST(:endDay AS double) + 0))")

Larkspur