Is it correct to use to_date() in hibernate if dat

Question

I have a table "Products(id, name, productionDate)" in Oracle database. "date" column is defined as String and it has value like "131120" (1st two digits for year, 2nd two digits for year & final two digits for date). I need a sorted output based on date using Hibernate. SQL query to do this is like the following:

select * from products order by to_date(productionDate, 'YYMMDD') desc;

This sql query is fine when I try from command prompt.

But when I tried to use this query using Hibernate, I am unable to retrieve the information and i got some error. My Hibernate code is like the following:

.......

String queryString = "from products p order by to_date(p.productionDate, 'YYMMDD')  desc";
Query query = s.createQuery(queryString);

.....

And I got the error like (Only some lines are given below which is helpful to understand)

Exception in thread "main" org.hibernate.exception.DataException: could not execute query
......
Caused by: java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected

Bean Definition is like the following:

......

String id;
String name;
String productionDate;

// getter and setter

Doesn't HQL support to_date()? If not, what is the way to find this type of query output?

Answer 1

HQL does not support to_date - at least according to docs HQL expressions. If all of the dates are greater than Jan 1, 2000, you can sort by the raw string value instead of converting.

You can use a native sql query using to_date. Look at the log output at org.hibernate.SQL to make sure that hibernate is not making too many unnecessary queries on joined entities.