Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Earnest Ravikumar
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry the exception is,

Hour must be between 0 and 23
 
Jaikiran Pai
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post the entire exception stacktrace and the relevant hibernate entity and HQL? Also which exact version of Hibernate are you using?
 
Earnest Ravikumar
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic