1

Since I want to store date and time for each data in table X, I use

@Temporal(TemporalType.TIMESTAMP)
private Date dateProcess;    //java.util.Date

and this work great. But now I want to create query that return all data in the same date. So I try

SELECT c from X c where c.dateProcess = : dateProcess

then I would pass in a java.util.Date for parameter dateProcess, but the returned result List is empty. I guess it must be that time comes into place here. So if the two dates does not have the exact date and time, it would not be equal, that would explain why my returned result list is empty. So how do I return a List of data base on a Date (dont care about time) in this scenario?

Thang Pham
  • 38,125
  • 75
  • 201
  • 285

4 Answers4

2

Just use a date range, specify the start and end time. > startTime and < endTime

James Scriven
  • 7,784
  • 1
  • 32
  • 36
  • Thank you. This answer my question. I posted these two links here showing specific instructions on how achieve what you suggested above. http://stackoverflow.com/questions/6611908/java-util-date-jodatime-given-a-java-util-date-generate-a-start-and-end-of-that and http://stackoverflow.com/questions/2856386/java-jpql-date-function-to-add-a-time-period-to-another-date – Thang Pham Jul 07 '11 at 14:42
1

Try TemporalType.DATE

If you need to retain the time, but only query for the date, then you have options:

  • use JPQL date function - day(..), months(..), year(..)
  • use < and > for date ranges (as James suggested)
  • store the date and time in two different columns.
Bozho
  • 588,226
  • 146
  • 1,060
  • 1,140
  • Sorry, I was busy the past couples days so I could not answer quick enough, and for it I apologize. If I switch to `TemporalType.Date`, do I lose the `time`? I want the date to display time in it as well. – Thang Pham Jul 05 '11 at 14:00
  • yes, you lose it. But you can store it in a different column, I guess. See updated – Bozho Jul 05 '11 at 14:01
  • would it be possible if I can ask you to show me a sample JPQL query using day(), months(), year() function? – Thang Pham Jul 05 '11 at 14:07
  • it's as if they are sql functions - use them with the field as argument to extract the day, month and year – Bozho Jul 05 '11 at 14:23
0

Does specifiying temporal type as Date help or would you lose precision?

f4lco
  • 3,728
  • 5
  • 28
  • 53
0

Currently I do like this

Using HQL to query on a date while ignoring the time on Oracle

I haven't try this one yet. So not sure it will work. date_format is registered function in release.

http://www.docjar.com/html/api/org/hibernate/dialect/MySQLDialect.java.html

Community
  • 1
  • 1
zawhtut
  • 8,335
  • 5
  • 52
  • 76