Hello,

I have a problem that I know someone else has solved before, but I just can't find the solution myself.

We're using Oracle 9i and named queries in Hibernate 2.1.7. One of the database column's type is a datetime, but we'd like to be able to force the where clause to use a TO_DATE style formatting mechanism to compare based on MM/DD/YYYY.

The query snippet is as follows:



I can trim :recDate in my code, but pw.record_date in some cases will have timestamp information, and in others it won't, so I'd like to discard that the way you would in Oracle:



I apologize if this is a trivial problem, but I did search the documentation and have come up empty-handed.

Thanks for any and all responses,
John

http://www.hibernate.org/hib_docs/v3/re ... yhql-where

You can use SQL functions in HQL.

Hi,

Is there a HQL implementation of the to_date function which will be translated into the corresponding syntax on different database types? In my named query I have a to_date() function from Oracle which I want to work on DB2 as well.

I m not sure whether i got your question or not.
Apologies if this is not what u r looking for.

you can try formula of the property element.
<property name="stringDate" formula="to_date(recDate,'MM/DD/YY') "

you ll then get a String object of Date in mm/dd/yy format.
you can then change it to Date format using SimpleDateFormat.

then u can compare two different dates.


Thanks

Thanks for the tip!

Actually I'm trying to write a HQL query here which can convert a string parameter into a date, and then do a row insertion on either Oracle or DB2 environment.

On Oracle SQL, I'll write the query as "insert into mytable values (TO_DATE(:dateVar,'YYYY-MM-DD HH24:MI:SS'))"

On DB2 SQL, I'll write the query as "insert into mytable values (TIMESTAMP_FORMAT(:dateVar, 'YYYY-MM-DD HH24:MI:SS'))"

I'm looking for a HQL function that will be converted to the corresponding Oracle or DB2 syntax, depending on the dialect used.

Thanks,
Alvin