This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes 24 Hour Format In HQL - 00:00:00 returned as 24:00:00 using HQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "24 Hour Format In HQL - 00:00:00 returned as 24:00:00 using HQL" Watch "24 Hour Format In HQL - 00:00:00 returned as 24:00:00 using HQL" New topic
Author

24 Hour Format In HQL - 00:00:00 returned as 24:00:00 using HQL

Earnest Ravikumar
Greenhorn

Joined: Aug 30, 2010
Posts: 3
Hi All,

This is my first post in this forum. I have some problem with HQL.

We have oracle database which stores date in 24 hour format. While fetching the date value from the table using to_date, the value 00:00:00 is returned as 24:00:00 by Hibernate query.

When this value is passed to a query it throws an exception

Number must be between 0 and 23.

I can very well understand that 24:00:00 cannot be parsed and that is the reason for the exception. How to convert the value from db to a correct format.

The value from in db is '2010-08-30 00:00:00'.
But it is being returned as '2010-08-30 24:00:00'.

Hope i made things clear. Please help me with the conversion.

Thanks in advance

Earnest Ravikumar
Greenhorn

Joined: Aug 30, 2010
Posts: 3
Sorry the exception is,

Hour must be between 0 and 23
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9956
    
163

Can you post the entire exception stacktrace and the relevant hibernate entity and HQL? Also which exact version of Hibernate are you using?

[My Blog] [JavaRanch Journal]
Earnest Ravikumar
Greenhorn

Joined: Aug 30, 2010
Posts: 3
Hibernate Version : hibernate-distribution-3.3.1.GA

HQL Query : Select max(rfc.lastRunDt) from RunFileControl rfc


Property in Hibernate Entity :

<property name="lastRunDt" type="timestamp">
<column name="LAST_RUN_DT" length="7" />
</property>



Exception:


2010-08-31 10:40:08,177 WARN [org.hibernate.util.JDBCExceptionReporter] - <SQL Error: 1850, SQLState: 22008>
2010-08-31 10:40:08,177 ERROR [org.hibernate.util.JDBCExceptionReporter] - <ORA-01850: hour must be between 0 and 23
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9956
    
163

HQL Query : Select max(rfc.lastRunDt) from RunFileControl rfc


I don't see how that query would result in Hibernate setting any date values. I believe it would translate to select max(date_column) from table_name. Do you have "show_sql" enabled in Hibernate? If not, can you enable that and get the SQL that's being generated? Also when you run the following SQL from a database client, what result do you get:



(replace date_column and table_name with appropriate names).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: 24 Hour Format In HQL - 00:00:00 returned as 24:00:00 using HQL
 
Similar Threads
date format problem
Unable to fetch records by using 'where' clause for a date type field in sql
Date format
'07/01/2010' could not be understood as a date.
Date comparision in SQL from timestamp