Hi,
I am using oracle as a backend. I am trying to select all the records which fall under a particular date range.
The problem is it is not generating the query as per I require. And gives me no record found, Whereas there are record falling in that range.
It is not even fetching records for the given date also.
Do I have to explicitly use the Oracle SQL specific keywords to accomplish this?
like "TO_CHAR" OR "TO_DATE"
regards
prasad chandrasekaran

How about posting your code so we can help out

Alternatively see http://forum.hibernate.org/viewtopic.php?t=925396 for a discussion on a similar topic

You can use what ever functions you would normally use in your sql where clause in HQL if appropriate. You can enable the SQL output by setting the showsql property to true.

Hi,
Thanks for your reply.
below is the code I want to execute


Date createDate=new Date();
Query query=session.createQuery("from Agent agt where agt.createDate=:createDate");
query.setDate("createDate", createDate);
if(query.list()!=null && query.list().size()>0){
//do something over here
}

What I think is that "setDate" function should be handled by the oracle dialect. isn't that true? Well I can create my own custom date format class? But if you have solution. That would be of greater help.

regards
prasad chandrasekaran

Yeah I can use any sql functions to get the output.
But I want it to be platform independent. That is if tomorrow I think of switching from Oracle to MS Sql it shouldn't give me any problem. Otherwise I would have to go and change each sql.

The query generated is simple
Select a.* from tblmst_agent a where a.createdate=?

I don't know but I think that it substitutes the long format date with the "?"

The query which needs to be generated is something like this

select a.* from tblmst_agent where to_char(a.createdate, 'yyyy-mm-dd') = '2001-12-31'
I think this is the right one.

regards
prasad chandrasekaran

Works better if you set the parameter as Timestamp rather than a Date:



If you set the parameter as a Date, Hibernate will handle it as an sql.Date and consider only the date part (without time).

I guess all comes from the fact that the sql package defines separate classes to hold date-only (date type), time-only (time type) and date-time up to the nanoseconds (timestamp type).

When setting the parameter to the Query, you have to consider the parameter type taking into account Hibernate's type definition as described in http://www.hibernate.org/hib_docs/reference/html/or-mapping.html#or-mapping-s2 (4.2.2 Basic value types)

- Hibernate Date type maps to a java.sql.Date (date only, no time)
- Hibernate Time type maps to a java.sql.Time (time only, no date)
- Hibernate Timestamp type maps to a java.sql.Timestamp (date and time with precision to the nanosecond)

Unfortunately, all these three java.sql types maps to the same java.util.Date. So it is up to you to specify wich precision you want.

I agree that we should retain as much cross platform support as possible but this type of function is currently not supported by HQL so you are choosing to use the DB vendors version. Now we are going to review the SQL92 functions and try and support them. The difficulty will be DB vendors that don't have the full support as we will need to alias those functions. The new parser will help in this area.

I agree with you David, but in this case, I think the problem can be solved by choosing carefully the types in the mapping definition and when building queries (as described above). And it remains portable ;-)

Moreover, people should also be aware of the particularities of java.sql.Timestamp as described in the note section of http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Timestamp.html

Thanks to all of you for your help.
We are using Oracle 8i which doesn't support ANSI queries. Think I got the problem.
I changed my HQL to

from agent agt where TO_CHAR(agt.createDate, 'YYYY-MM-DD') > SOMEDATE

I think this should work anyway I would be trying your example also
regards & respect
prasad chandrasekaran