Hibernate query date compare month only

3
votes

I need to grab some records from a MySQL table which has a Column posted_date which is a Timestamp. I need to filter records by comparing against month only. For example I want all the records posted this month only.

I tried as given in this link Hibernate query date compare on year and month only

But I get following error

line 1:106: unexpected token: to_char
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:769)

How to compare only with month in HQL by taking month as parameter?

1
javamysqlspringhibernate

1 Answers

7
votes

Use MONTH(...) HQL function like this:

select so from SomeObject so where MONTH(so.date) = MONTH(:date)

or (simple but not database agnostic - works with Oracle/PostgreSQL/H2)

select so from SomeObject so where TO_CHAR(so.date, 'MM') = :month_as_string