aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate Date Issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate Date Issue" Watch "Hibernate Date Issue" New topic
Author

Hibernate Date Issue

Ingoba Ningthoujam
Ranch Hand

Joined: Dec 04, 2006
Posts: 90
Hi,

I am using a Hibernate query for getting data from a table between two DATES.
e.g., between 20/07/2007 to 31/08/2007. The problem is Hibernate is not taking the END date. Its taking date before the end date i.e., 30/08/2008.

I am using AND.My Query is




Please anyone help me.
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10102
    
165

The problem is Hibernate is not taking the END date. Its taking date before the end date i.e., 30/08/2008.


Do you mean, you are passing 31/08/2008 as part of the HQL query and hibernate is changing it to 30/08/2008 while generating the SQL? Can you post the SQL query that is getting generated?

And is the behaviour same, if the end date is something else? Could you try specifying the end date as 31/01/2008 and see the output?


[My Blog] [JavaRanch Journal]
pradeep jaladi
Ranch Hand

Joined: Nov 21, 2004
Posts: 65
Hi,

I guess this is problem related to local, i guess the enddate which is send is created in one GMT and the date based GMT Format is set to another. Can you check that and get back.


Pradeep.Jaladi
Ingoba Ningthoujam
Ranch Hand

Joined: Dec 04, 2006
Posts: 90
Thanks Jaikiran. But the problem is not converting to 30/08/2008. The problem is while firing the HQL

Select * from CpsChangeRequest ccr where ccr.dateCreated between '01/08/2008' and '31/08/2008'

only data up to 30/08/2008 is retrieved. I couldn't get data for the date '31/08/2008'.
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10102
    
165

You will have to test out a couple of things (as i mentioned in my earlier post).

1) What is the exact SQL query that gets generated out of this HQL? You will find this in the log files (i guess show_sql should be turned on in the hibernate config file)

2) How does this query behave with dates before Febraury 28 2008. Remember that 2008 is a leap year and i have seen JDK bugs being reported related to leap year. If this turns out to be because of the JDK you are using, you will have to upgrade to a latest JDK.
Stevi Deter
Ranch Hand

Joined: Mar 22, 2008
Posts: 265

Ingoba,

What is the data type of the date column you're querying against? Does it include a full time stamp?

A possible problem, if that's the case, is your method of passing a date string with no time information may be resulting in passing in a end date for your between that is set to date + time of 12:00:00 am, which would probably exclude any actual records for the day (e.g., they happened after the clock struck midnight).


There will always be people who are ahead of the curve, and people who are behind the curve. But knowledge moves the curve. --Bill James
Ingoba Ningthoujam
Ranch Hand

Joined: Dec 04, 2006
Posts: 90
Thanks Stevi Deter.

I am not taking the full time stamp. Actually I am taking the DATE from a javascript popup window as a string.
Are you sure that timestamp will work my requirement?

Please reply.
Stevi Deter
Ranch Hand

Joined: Mar 22, 2008
Posts: 265

Ingoba,
The first question to answer is: what is the value stored in the database column you are querying against? Is it a pure date, or a date and time field?

If it is the latter (e.g., SQL Server datetime), then I would suggest modifying the creation of your query to take the selected date from your javascript element and add a time of 23:59:59 to make sure it gets all records for the day.

If this is not the case (you're querying a field that has just date information and no time information) then this probably isn't your problem.
Ingoba Ningthoujam
Ranch Hand

Joined: Dec 04, 2006
Posts: 90
Thank you very much Stevi Deter.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate Date Issue