我的数据库有字符串类型的'm_date‘，并将日期存储为“”格式。现在，我希望借助过滤器之间的JPA标准查询从表中获取记录。既然m_date存储字符串日期，我就不能直接使用criteriaBuilder.between函数。但在此之前，我需要使用函数转换( Date，m_date，103)将其转换为date。我试着把字符串日期转换成日期。

SQL查询如下所示

SELECT myId from table where status='PENDING' and CONVERT(date, m_date,103) between fromDate and toDate)

标准API

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> criteriaQuery = criteriaBuilder.createQuery(MyEntity.class);
Root<MyEntity> root = criteriaQuery.from(MyEntity.class);
Predicate statusPredicate = cb.equal(root.get("status"), cb.literal("PENDING"));
Expression<String> convertFunction = cb.function("CONVERT", String.class, cb.literal("date"), root.get("mDate"), cb.literal(103));
Predicate datePredicate = cb.between(convertFunction, fromDate, toDate); // fromDate and toDate coming from request...
CriteriaQuery<MyEntity> myQuery = criteriaQuery.select(root.get("myId")).where(statusPredicate, datePredicate);
TypedQuery<MyEntity> query = entityManager.createQuery(myQuery);
return query.getResultList();

我遇到了类似这样的错误

Hibernate: select myentity_0.my_id as col_0_0_ from table where myentity_0.status=? and (CONVERT(?,myentity.m_date,103) between ? and ?)
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P1'.

有人能帮我吗，我怎么能把“日期”作为转换函数的第一个参数？我已经被困了这么久了。我会很感激你的帮助。提前谢谢。