How to search current date in Timestamp field of MYSQL using Hibernate Named Query (HQL)?

Question

I tried to pass date using following way but unable to succeed. dateAnswered is field which stores date as 2017-09-13 00:00:00.

Can anyone tell where i am getting wrong:

Way 1: SetParameter

@NamedQuery(name = "findAnswar", query = "SELECT a FROM Answar a WHERE (a.dateAnswered = :currentDate )"

Try 1: 
super.em.createNamedQuery( "findAnswar" , Answar .class ).setParameter( "currentDate", new Date() ); //Not receive any data

Try 2: 
Date date = new Date();
date.setHours(0);
date.setMinutes(0);
date.setSeconds(0);
super.em.createNamedQuery( "findAnswar" , Answar .class ).setParameter( "currentDate",  date) //Not receive any data

Way 2: Set inbuilt parameters

@NamedQuery(name = "findAnswar", query = "SELECT a FROM Answar a WHERE (a.dateAnswered = current_date() )") //NOT WORKS

It works if i do add Temporal to getter:

@Column(name = "DATE_ANASWERED")
@Temporal(TemporalType.DATE)
 public Date getDateAnswered () {
  return dateAnswered ;
}

BUT IT GIVES another issue : Caused by: org.hibernate.HibernateException: Wrong column type in db.ANSWAR for column DATE_ANASWERED. Found: datetime, expected: date

 @NamedQuery(name = "findAnswar", query = "SELECT a FROM Answar a WHERE (a.dateAnswered = current_date )") //NOT WORKS - JPA errors

Even in previous answers of Stackoverflow i am not able to find any related solution. Can anyone help in fixing the same using Named Query only?

What error messages you get in first example? – olegsv Oct 18 '17 at 10:38 — olegsv, Oct 18 '17 at 10:38
@olegsv Not receive any data.. – Harpreet Oct 18 '17 at 10:47 — Harpreet, Oct 18 '17 at 10:47

score 2 · Accepted Answer · answered Oct 20 '17 at 11:00

2

Able to achieve it doing following changes to named query and it worked like charm:

"SELECT a FROM Answar a WHERE cast(a.dateAnswered as date) = current_date()

As underlying type is datetime so so we need to cast it to date to use current_date()

answered Oct 20 '17 at 11:00

Harpreet

186
1
1
10

Tom · Answer 2 · 2017-10-20T07:46:46.757

0

You need to specify the temporal type.

Way 1

super.em.createNamedQuery("findAnswar", Answar.class)
     .setParameter("currentDate", new Date(), TemporalType.TIMESTAMP);

Way 2

@Column(name = "DATE_ANASWERED")
@Temporal(TemporalType.TIMESTAMP)
    public Date getDateAnswered () {
    return dateAnswered ;
}

edited Oct 20 '17 at 07:46

answered Oct 18 '17 at 15:31

Tom

977
11
18

Thanks looking very promising but it did not work either as default bean is @Column(name = "ANTAS_AVSLUTTES") public Date getDateAnswered () { return dateAnswered ; } and i could not mention TemporalType on this entity because in MYSQL it is DateTime datatype. – Harpreet Oct 20 '17 at 04:24
unable to edit above comment: Sorry column name is @Column(name = "DATE_ANASWERED") here – Harpreet Oct 20 '17 at 04:30
ok my mistake the TemporalType needs to be TIMESTAMP by a DateTime datatype so i updated my answer – Tom Oct 20 '17 at 07:48

How to search current date in Timestamp field of MYSQL using Hibernate Named Query (HQL)?

2 Answers2