Hibernate query date compare month only

Issue

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?

Solution

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

Answered By – kemot90

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0