如何从HQL查询中的当前日期减去天数

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

我正在使用HQL从现在起21天内获取插入的数据。这是我的代码

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;

我不能使用createSQLQuery。现在我没有得到任何数据，但是有日期2016-06-20的数据。这是因为月份改变了，因为当我使用CURDATE()-7时，我得到了日期2016-07-04的正确数据。dat的计算为:

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:

我还使用了针对本机sqlQuery的INTERVAL。下面是我在HQL中使用INTERVAL的代码:

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.

但是它给了我一个异常，比如: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

我可以用什么来代替像这样减去日期:CURDATE()-21?在HQL只有

2 个解决方案

#1

You can use date_Sub

您可以使用date_Sub

 "from Users where createdDate =   DATE( DATE_SUB( NOW() , INTERVAL 21 DAY ) )"

#2

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

我已经通过使用一个本地SQL查询解决了这个问题，该查询可以得到确切的日期。

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:

然后我在HQL查询中使用这些数据如下:

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

#1