Search code examples
javaspring-bootjpacriteria

Problem when using lessThanOrEqualTo() of Predicate when compare time

Source code

Expression<LocalDateTime> dateTimeFromDatabase = root.get("period").as(LocalDateTime.class);
Expression<LocalTime> timeFromDatabase = dateTimeFromDatabase.as(LocalTime.class);
LocalTime periodToRequest = FormatUtils.getLocalDateTime(request.getPeriodTo(), "dd-MMM-yyyy HH:mm:ss").toLocalTime().plusMinutes(30);
Predicate predicate = criteriaBuilder.lessThanOrEqualTo(timeFromDatabase, periodToRequest.plusMinutes(30));

I have a column in database that datatype is datetime. I using JPA, Specification to get it with Criteria. In my request, I pass in "period_to" ex: "31-Mar-2023 07:30:00" I wanna to implement dynamic filter, that compare the only time that I pass in request("07:30:00") with the time that I take from database. And I meet the error: com.microsoft.sqlserver.jdbc.SQLServerException: The data types time and datetime are incompatible in the less than or equal to operator.

  • The datatime in database is 2023-03-31 08:00:00.000.
  • The period_to that I pass in is 31-Mar-2023 07:30:00.
  • I wanna compare 07:30:00 with 08:00:00.

I need your help, thank you!

Solution

  • One of 2 known solution: In spring.data.url, concate to end of string

    sendTimeAsDateTime=false

    for example, original

    spring.datasource.url=jdbc:sqlserver://DESKTOP-EQBT80R\\MSSQLSERVER:1433;database=FooSample;trustServerCertificate=true;

    change to

    spring.datasource.url=jdbc:sqlserver://DESKTOP-EQBT80R\\MSSQLSERVER:1433;database=FooSample;trustServerCertificate=true;sendTimeAsDateTime=false;

    Let's share with your result.