使用日期字符串作为参数的 SQL 查询不起作用

Question

我试图写jpa查询，但我不能。我试着:

@Query(value="select count(id_ass) from assenza where id_dip=?1 and data_ass between ?2 and ?3", nativeQuery = true)
Integer getAssenzeByid_dipmese(int id_dip,String inizio, String fine);

但我得到ERROR: operator does not exist: date >= character varying

然后我尝试使用

@Query(value="select count(id_ass) from assenza where id_dip=?1 and data_ass >= ?2 and  data_ass<= ?3", nativeQuery = true)
Integer getAssenzeByid_dipmese(int id_dip,String inizio, String fine);

但是我得到了相同的错误

然后我试图转换日期中的字符串，但它不起作用…谁能告诉我我哪里做错了?由于

Answer 1

由于变量是string，因此它们作为varchar传递。您必须显式地将varchar转换为日期。

@Query(value="select count(id_ass) from assenza where id_dip=?1 and data_ass between CAST(?2 AS DATE) and CAST(?3 AS DATE)", nativeQuery = true)
Integer getAssenzeByid_dipmese(int id_dip,String inizio, String fine);