MySQL 没有时间戳的 HQL 中的日期比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25680237/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date Compare in HQL without timestamp
提问by Sanket Dosi
I have to compare two dates in hibernate hql query. I am using java.util.Date in my java bean and using timestamp as datatype in mysql database.
我必须在 hibernate hql 查询中比较两个日期。我在我的 java bean 中使用 java.util.Date 并在 mysql 数据库中使用时间戳作为数据类型。
select t from Task t where t.modifiedDate > t.endDate;
Above query compares time with date. What should i do to compare date in above query without time.
以上查询将时间与日期进行比较。我应该怎么做才能在没有时间的情况下比较上述查询中的日期。
回答by carbontax
See the Hibernate documentations for available date functions
有关可用的日期函数，请参阅 Hibernate 文档
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html
In section 14.10 notice this line
在 14.10 节注意这一行
second(...), minute(...), hour(...), day(...), month(...), and year(...)
So this should work
所以这应该有效
... where year(t.endDate) > year(t.startDate)
and month(t.endDate) > month(t.startDate)
and day(t.endDate) > day(t.startDate)
The solution reported as satisfying the question is
报告为满足问题的解决方案是
... where DATE(t.endDate) > (t.startDate)
回答by kundwiramurs
The proposed solution with the date()function does not seem to be pure SQL and does not work for me (using SQL Server). The original solution (comparing day, month and year individually) is a step in the right direction, but cannot be done in this way, as, e.g., for a date to be in the future, a day need not be greater if the month is greater, etc. The following SQL, however, should work:
建议的具有该date()功能的解决方案似乎不是纯 SQL 并且对我不起作用（使用 SQL Server）。最初的解决方案（分别比较日、月和年）是朝着正确方向迈出的一步，但不能以这种方式完成，例如，对于未来的日期，如果月更大，等等。 但是，以下 SQL 应该可以工作：
( year(t.endDate) > year(t.startDate) )
or
( year(t.endDate) = year(t.startDate)
and
month(t.endDate) > month(t.startDate) )
or
( year(t.endDate) = year(t.startDate)
and
month(t.endDate) = month(t.startDate)
and
day(t.endDate) > day(t.startDate) )
