DateAdd function not support in jpa query

Question

DateAdd function is working fine as a sql query in sql-server as i want to substract some number of days from a date and i am getting result but while same i am using in jpa project, spring boot project has not started.

Below the repository class, if i comment out this below line of code, spring boot project starts as expected.

public interface domainRepository extends CrudRepository<domainTable , Long> {

    @Query("Select DATEADD(day,-(1), d.date) from  domainTable d "
           + "where d.id in (:id)")
    public Date getDate(@Param("id") Long id); 

}

How to fix this? or i do have to write a separate function instead of DATEADD?

"or else" what? Issuing threats isn't really a way to motivate people. The JPA spec is public, and all JPA docs would tell you that "DATEADD" is not valid in JPQL (though it is valid in SQL). Those same JPA docs would tell you about a `FUNCTION` function that allows you to invoke any SQL function. Why not read a bit? Or why not just do the date addition in JAVA?! — , May 01 '18 at 14:41
@DN1, I think you need to relax a little, that was most likely nothing but poor English skills. Other than that I agree about doing the date math in java. — Joakim Danielson, May 01 '18 at 14:44
First of all thank you for replying me. I really need some help. I can do date addition /subtraction using Calendar but i am more into doing in jpql itself and return the date. Please suggest me how i can do that. — Kaustav, May 01 '18 at 14:52
The first responder already did tell you what to do ... look up JPQL `FUNCTION`. There are ample posts on Stackoverflow even about it, with people thinking that JPA takes in SQL. https://stackoverflow.com/questions/30937839/how-to-call-like-any-postgresql-function-in-jpql — , May 01 '18 at 15:03
@BillyFrost May i request you please write the code.I am not able to understand clearly. — Kaustav, May 01 '18 at 15:19
Possible duplicate of [Java: JPQL date function to add a time period to another date](https://stackoverflow.com/questions/2856386/java-jpql-date-function-to-add-a-time-period-to-another-date) — Dherik, Aug 07 '18 at 19:27

score 3 · Answer 1 · answered May 31 '18 at 11:35

3

Actually, JPA doesn't support time periods operations because not all databases support it. So you have following options:

1- Calculate date programmatically (Java side, using calendar API or Java 8 Date Time API).

2- Use native query.

answered May 31 '18 at 11:35

Nour Eddine

131
2
6

Native query is not stranded way to use i think so, only have to use hql query. – Kaustav Jun 12 '18 at 08:46
Please see my answer. JPQL now does support time period operations. – Josep M Beleta Sep 22 '20 at 01:21

score 0 · Answer 2 · answered Sep 14 '20 at 14:02

At least in the Hibernate (5.3.7) implementation of JPA 2.2 this JPQL query works perfectly for MySQL:

TypedQuery<Usuario> query2 = getEntityManager().createQuery("select distinct p.usuario from Peticion p"
    + " where p.deletionDate is null and p.fechaAprobacion is not null"
    + " and ?1 between subdate(p.fechaInicio, 'interval 1 microsecond') and"
    + "                adddate(p.fechaFin, 'interval 1 day')", Usuario.class);

Date dateDate = Date.from(date.atStartOfDay(ZoneId.systemDefault()).toInstant());
query2.setParameter(1, dateDate, TemporalType.DATE);

DateAdd function not support in jpa query

2 Answers2