How to use TO_DATE SQl function with Criteria API

pavan_vallur · **Posted:** Wed Jun 11, 2008 4:30 pm

Query:

select SALARY
from ACCOUNTS
where TO_DATE(SALARY_DATE, 'MM/DD/YYYY') = :salDate

Note:SALARY_DATE is a timestamp field in DB2

Some one help me, how to build this query in Hibernate Criteria API.

Cameron McKenzie · **Posted:** Thu Jun 12, 2008 9:33 am

Well, there's a few ways to go. Of course, the Criteria API is object based, so much depends on how many objects you have.

This might easily be accomplished by creating an Accounts objects an using a greater than or less than Restriction on the salary range or date range, or whatever property you want to place a restriction on.

Here's a quick but very good tutorial on using the Criteria API. There are some simple examples that should give you enough ideas on how to customize a solution for your code:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

pavan_vallur · **Posted:** Thu Jun 12, 2008 1:36 pm

Yaa I can use tht Restriction(less than Or Greater than for the Date Range).

BUT iS IT POSSIBLE TO USE TO_DATE OR DATE(in DB2) sql functions using CRITERIA API's ?

Note: I used this function TO_DATE with normal HQL query and it works..

rk1891 · **Joined:** Sat Jun 14, 2008 6:42 pm **Posts:** 4

I'm also looking how to use TO_DATE function in Criteria.

Here is the requirement -

I'm adding code like this

Code:

criteria.add(Restrictions.eq("insertdate", date));

It is generating the SQL like below

Quote:

where insertdate = '22 OCT 2008'

Which in turn returns no records.

If i'm able to use TO_DATE function there then it could've returned records

Anybody has an idea?Pls respond.

Thanks,
Raj

rk1891 · **Joined:** Sat Jun 14, 2008 6:42 pm **Posts:** 4

We can add literal sql queries to Critiria like below..

Code:

criteria.add(Restrictions.sqlRestriction("to_date({alias}.insertdate,'dd/mm/rrrr') = "+dateToStringFormat(date)));

and make sure that the dateToStringFormat method returns String as
to_date('22/10/2008', 'dd/mm/rrrr').

The result would be
and to_date(this_.insertdate,'dd/mm/rrrr') = to_date('22/10/2008','dd/mm/rrrr')

The {alias} is required only if multiple tables are used in the query and the same column name exits in those tables.

Hope this helps atlease those who will search for this functionality from now on..

Raj

stephaneeybert · **Joined:** Mon Apr 16, 2007 8:10 am **Posts:** 246

@rk1891 Your solution would work on one database system only.

Anyway to have it database system agnostic ?