mysql - 使用日期字符串作为参数的 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);

但我得到同样的错误

然后我尝试将字符串转换为日期，但它没有用......有人可以告诉我我做错了什么吗？谢谢

标签： mysqlsqlspring-boothibernatejpa

Answer 1

由于变量是字符串，因此它们作为 varchars 传递。您必须将 varchars 显式转换为日期。

@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);