Search code examples
javamysqlhql

How to Subtract number of days from current date in HQL query

I am using HQL to get the data inserted exact 21 days from now. Here is my Code

Query queryThreeWeek = session.createQuery("from Users where createdDate = CURDATE()-21");
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();

I can not use createSQLQuery. Right now I am not getting any data, but there is data for the date 2016-06-20. And that is because of the month changed because when I used CURDATE()-7 I got the correct data of the date 2016-07-04. The calculation for dat is like;

2016-07-11 - 7 = 20160704
2016-07-11 - 21 = 20160690

I also Tired using INTERVAL which is for native sqlQuery. Here is my code for using INTERVAL in HQL: 

Query queryThreeWeek = session.createQuery("from Users where createdDate = DATE( DATE_SUB( NOW() , INTERVAL 21 DAY ) )");
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();

Also tried

Query queryThreeWeek = session.createQuery("from Users where createdDate = DATE( DATE_SUB( CURDATE() , INTERVAL 21 DAY ) )"); 
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();

but it is giving me exception like: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: 21.

So what can I use instead of subtracting the day like this: CURDATE()-21? in HQL only

Solution

  • I have solved the issue by using one native SQL query which can get me the exact date.

    Query sub3Week = session.createSQLQuery("select DATE( DATE_SUB( CURDATE() , INTERVAL 21 DAY ) ) from dual");
List<Date> sub3WeekList = sub3Week.list();

    And then I use this data in the HQL query like this:

    Query queryThreeWeek = session.createQuery("from Users where createdDate = :createdDate");
queryThreeWeek.setParameter("createdDate", sub3WeekList.get(0).toString());
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();