Q Operand of - is of type 'java.lang.Object' X which is not a numeric type (it is not an instance of 'java.lang.Number', 'java.time. Temporal', or

Post springboot upgrade to 3.2.4 the jpql query with expression date(current_date) - date(entity.modDate) is not working and is part both Select and where clause.
Also in where clause we are checking if the difference is IN(25,26,27,28,29,30)

You will have to be more specific than that. Please post the entity model and the full query as well as the full stack trace that you’re seeing.

Hello, @Jazzzzyyy
After upgrading to Spring Boot 3.2.4, if you’re encountering issues with JPQL queries involving date calculations, it’s possible that there have been changes in how dates are handled. To resolve the issue with the expression date(current_date) - date(entity.modDate) not working, consider the following steps:

Ensure that the JPQL syntax for date operations is correct. JPQL may not support direct date subtraction. You might need to use a database-specific function or JPQL’s FUNCTION keyword to call a native SQL function for date subtraction.
If your database supports it, use the DATEDIFF function to calculate the difference between dates. For example:
@Query(“SELECT e FROM Entity e WHERE FUNCTION(‘DATEDIFF’, current_date, e.modDate) IN :days”)
List findEntitiesWithDateDifference(Set days);
Replace Entity with your actual entity class and days with a parameter containing the set of day differences you’re interested in.
Ensure that you’re using a compatible version of Spring Data JPA with Spring Boot 3.2.4. Incompatibilities might lead to unexpected behavior.

I hope my information is helpful to you.

Entity
Public Class User{

Id
GeneratedValue(strategy=Generation type.IDENTITY)
private Long id;

private String userId;
private String phoneno;
private String modDate;
private Boolean flag;

//Default constructor
}

UserRepo

public interface UserRepo extends Jpa repository<User, Long>{

Query(" Select New entity.User(u.userId, u.phoneno, (Date(current_date) - date(u.modDate))) from User where Date(current_date) - date(u.modDate) IN (25,26, 28, 30) and user.flag=false ")
List findLastModUserId();

//Some code logic
}

stack trace
Caused by: org.hibernate.query.SemanticException: Operand of is of type ‘java.lang.Object’ which is not a numeric type (it is no TypecheckUtil.assertOperable (TypecheckUtil.java:499)

at org.hibernate.query.sqm.internal. at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitAdditionExpression (SemanticQueryBuilder.java:2958)

at org.hibernate.grammars.hql.HqlParser$AdditionExpressionContext.accept(HqlParser.java:711 r.java:7113)

org.hibernate.query.hql.internal.SemanticQueryBuilder.visitGroupedExpression (SemanticQueryBuilder. java:3038)

at grammars.hql.HqlParser$GroupedExpressionContext.accept(HqlParser.java:7231)

at org.hibernate.grammars. org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)

at visitExpressionor (Halparser sevisitor.java:856)

at at org.hibernate.grammars.hql.HqlParser$ExpressionOrPredicateContext.accept(HqlParser.java:7927) at org.antir.v4.runtime.tree.AbstractParseTreevisitor. visitChildren(AbstractParseTreevisitor.java:46)

at org.hibernate.grammars.hql.HqlParserBaseVisitor. visitInstantiationArgumentExpression(HlParserBaseVisitor.java:371) at org.hibernate.grammars.hql.HqlParser$InstantiationArgumentExpressionContext.accept(HqlParser.java:4012)

org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInstantiationArgument(SemanticQueryBuilder.java:1426)

at at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInstantiation (SemanticQueryBuilder.java:1398)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInstantiation (SemanticQueryBuilder.java:269)

at .hibernate.grammars.hql.HqlParser$InstantiationContext.accept(HqlParser.java:3761)

.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectableNode(SemanticQueryBuilder.java:1355) at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelection(SemanticQueryBuilder.java:1309)

at

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectClause(SemanticQueryBuilder.java:1302)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1154)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression (SemanticQueryBuilder.java:941) org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression (SemanticQueryBuilder.java:269)

at at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1869)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup (SemanticQueryBuilder.java:926)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:269) at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1740)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement (SemanticQueryBuilder.java:443)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement (SemanticQueryBuilder.java:402)

at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel (SemanticQueryBuilder.java:311) at org.hibernate.query.hql.internal.StandardHq1Translator.translate (StandardHq1Translator.java:71)

at org.hibernate.query.internal. QueryInterpretationCacheStandardImpl.createHqlInterpretation (QueryInterpretationCacheStan

at org.hibernate.query.internal.Query InterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheSta

at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:790)

at org.hibernate.internal.AbstractSharedSessionContract.createQuery (AbstractSharedSessionContract.java:840)

71 common frames omitted

Hi Ryan1969 ,
I have tried above way as well.Datediff is not identified as a function.We are using Postgres db.
Thanks,
Jazzzzyyy

Hello, If you want any help please tell me. I am happy to help you.

Best Regard,
ryan1969 California Healthcare

@Jazzzzyyy you should be able to use the datediff or timestampdiff functions with the following syntax across all Hibernate supported databases: datediff(day, current_date, e.modDate).