The data types datetime and time are incompatible

Question

I have a variable type time in a column of a table of the database. How can I compare this value in java with this field I mean can i use date, gregoriancalendar? I've tried adn I still have this message, please can someone give me an advice

Date d2 = new Date();                      // timestamp now
Calendar cal = Calendar.getInstance();       // get calendar instance
cal.setTime(d2);                           // set cal to date
cal.set(Calendar.HOUR_OF_DAY, 10);            // set hour to midnight
cal.set(Calendar.MINUTE, 30);                 // set minute in hour
cal.set(Calendar.SECOND, 0);                 // set second in minute
cal.set(Calendar.MILLISECOND, 0);            // set millis in second
Date d3 = cal.getTime();   

@SuppressWarnings("unchecked")
List<Asistencia> list = (List<Asistencia>) sessionFactory
.getCurrentSession()
.createQuery(
        "select new Asistencia( asis.idAsistencia,"
        + "asis.horaInicio, asis.horaFin) "
        + "from Asistencia asis "
        + "where :hour >= asis.horaInicio and :hour <= asis.horaFin")

.setParameter("hour", d3).list();

I also used between

where :hour between asis.horaInicio and asis.horaFin

and the mesage is the same:

ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - The data types datetime and time are incompatible in the greater than or equal to operator.

The data types datetime and time are incompatible in the greater than or equal to operator.

Here the class Asistencia:

public class Asistencia implements java.io.Serializable {

private static final long serialVersionUID = 1L;
private long idAsistencia;
    private Date horaInicio;
    private Date horaFin;
    private int idAula;
    private int idCurso;
    private int idPeriodo;
    private Date fecha;

    public Asistencia (){
    }
    public Asistencia (long idAsistencia, Date horaInicio, Date horaFin){
          this.idAsistencia
          this.horaInicio = horaInicio;
          this.horaFin = horaFin;
    }

}

Answer 1

It seems the only problem was I'm using SQL Server 2008 and is necessary to put sendTimeAsDateTime=false in the connections properties.

Here a similar question. comparing time in sql server through hibernate

Answer 2

Without setting that sendTimeAsDateTime property, which is not available for the SQL Server drivers older than 3.0 (and some of us are stuck with what we have, for reasons), you could try to use a String instead of a date. This worked for me using a PreparedStatement and I bet it would work in this scenario also. Change the last line of your first code block to:

.setParameter( "hour", new SimpleDateFormat("HH:mm:ss.SSS").format(d3) ).list();

Answer 3

I've encountered the same issue when querying data by entity property of type javax.time.LocalDate via spring-data-jpa and eclipselink . Connection setting sendTimeAsDateTime=false didn't help. This was fixed by adding spring-data-jpa converter <class>org.springframework.data.jpa.convert.threeten.Jsr310JpaConverters$LocalDateConverter</class> into the persistence.xml file. Hope this helps.

Answer 4

I also have the same issue. My sql data type is Time(7) and each time I want to compare it via JPQL query, that error comes out. Connection string sendTimeAsDateTime=false didn't work. Adding <class>org.springframework.data.jpa.convert.threeten.Jsr310JpaConverters$LocalDateConverter</class> into the persistence.xml also didn't work.

What I do is, store data Time(7) from sql into String, for example this is my table design on sql

StartTime   time(7) 

In my java class I store that field into String variable

@Entity
@Table(name = "tableSchedule")
public class TableSchedulue implements Serializable {
    private static final long serialVersionUID = -9112498278775770919L;
    ....
    @Column(name = "StartTime")
    private String startTime;
    .....
}

When I use JPQL query like this (in repository)

@Query(value = "SELECT a "
            + "FROM TableSchedule a "
            + "WHERE a.startTime <= ?1 ")
    List<TableSchedulue > getTestData(String startTime);

Your string format must HH:mm:ss
It works. Hope it helps

CONS : Because in SQL the type is Time(7) so value 08:00:00 in SQL will become 08:00:00.0000000 in persistence so you need to parse it into your needs