2

I have a defined column notification_date as DATETIME in Database.

When the user filter I get only Date. So I'm converting as data and time using Java. For an Example if the userSearch Date is '2012-09-26'. The final converted date and time format will be '2012-09-26 17:34:00'

When I want to search Date filter operations like equal,Less than , Greater than and date between, I'm not able to get the proper result using Hibernate criteria Query for Greater than and Less than operation.

The code used 

crit.add(Restrictions.gt("notificationDate",notificationVO.getNotificationDate()));

The restriction will change according to the condition

To compare only date, is there any functions available in Hibernate?

Any other idea to fix this issue?

DataNucleus
  • 15,497
  • 3
  • 32
  • 37
Prathap
  • 717
  • 2
  • 8
  • 25
  • I don't understand. The user enters 2012-09-26 as a date. Which notifications do you want? Those whose date/time is 2012-09-26 or later? Those whose date/time is 2012-09-27 or later? – JB Nizet Sep 27 '12 at 14:42
  • @JBNizet : It differs on the user search. I'm not getting solution for both. E,g less than 2012-09-26 and greater than 2012-09-26. – Prathap Sep 27 '12 at 19:43
  • I still don't understand. Why don't you answer my question? If the user enters 2012-09-26, which notifications would you like your query to retrieve? – JB Nizet Sep 27 '12 at 21:24
  • @JBNizet :If the user enters 2012-09-26 and search criteria as less than , I need all the dates which is less than 2012-09-26. – Prathap Sep 28 '12 at 06:32

1 Answers1

1

From your comment:

If the user enters 2012-09-26 and search criteria as less than, I need all the dates which is less than 2012-09-26

Then the restriction should be:

crit.add(Restrictions.lt("notificationDate", notificationVO.getNotificationDate()));

If the user enters the same date, and the search criterion "greater than", and you use the following restriction:

crit.add(Restrictions.gt("notificationDate", notificationVO.getNotificationDate()));

You will of course get all the entries with a date and time greater than 2012-09-26 00:00:00, including of course all the entries of the 2012-09-26 date.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • I need to compare only by the date. If search for greater than 2012-09-26 , the expected result is from 2012-09-27. And If search is for Less than 2012-09-26, the expected result is 2012-09-25 and lower. The solution which you gave was the question I posted. If I search for greater than I'm getting values from 2012-09-26 00:00:01. It should not be. – Prathap Sep 28 '12 at 10:50
  • 1
    That's why I asked you to tell me what you finally told me now, three times. If you want to get the notifications for the 27 and later, add one day to the date the user has entered, and execute the above query (with `ge` instead of `gt`) – JB Nizet Sep 28 '12 at 11:20
  • then how do I compare equals case? – Prathap Sep 28 '12 at 11:48
  • Get all the dates that are >= 2012-09-26 and < 2012-09-27 – JB Nizet Sep 28 '12 at 11:49
  • Solution works. thanks. I did using between criteria to compare date equals. I would like to know is there any function to compare only date , by discarding time. Like mysql function like date_format? – Prathap Sep 28 '12 at 12:08
  • Hi @alex , I have almost same problem as Prathrap, but the difference is what I need to do is format the column with the date values then perform a "like". I've already made a post but no one has answered it yet. Maybe you could check it here http://stackoverflow.com/questions/32062449/grails-criteria-how-to-select-all-columns-in-mysql-and-at-the-same-time-format Thank you. – user3714598 Aug 18 '15 at 03:08