sql query with string of a date as parameter doesn't work-CodePudding

I'm trying to write a query with jpa, but I can't. I tried:

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

but i get ERROR: operator does not exist: date >= character varying

then I tried to use

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

but I get the same error

then I tried to convert the strings in date but it didn't work... can somebody tell me what I'm doing wrong? Thanks

CodePudding user response：

Since the variables are Strings, they're passed as varchars. You have to explicitly cast the varchars as dates.

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