When i am trying to execute,the following query using hibernate
select * from from user where (date(MONTH || '-' || '01'|| '-' || YEAR) between '2005-01-01' and '2009-01-01')

I am getting the error

No authorized routine named "DATE" of type "FUNCTION" having compatible arguments was found.
[1/29/08 8:13:51:453 EST] 28dd20a3 JDBCException W org.hibernate.util.JDBCExceptionReporter SQL Error: -727, SQLState: 56098
[1/29/08 8:13:51:453 EST] 28dd20a3 JDBCException E org.hibernate.util.JDBCExceptionReporter An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-440", SQLSTATE "42884" and message tokens "DATE|FUNCTION".
[1/29/08 8:13:51:453 EST] 28dd20a3 JDBCException W org.hibernate.util.JDBCExceptionReporter SQL Error: -727, SQLState: 56098
[1/29/08 8:13:51:453 EST] 28dd20a3 JDBCException E org.hibernate.util.JDBCExceptionReporter An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-440", SQLSTATE "42884" and message tokens "DATE|FUNCTION".

Can any body tell me how to resolve this issue , I am very new to DB2 database
thx in advance
jomin

You didn't provide arguments for the MONTH and YEAR functions. In addition, MONTH and YEAR return integers. DB2 won't allow you to concatenate integers without using functions to convert them to character type data. I'm not sure I understand the intent here anyway. It looks like you're trying to format a month begin date, but your BETWEEN ranges spans years.

Hi jsharon1248,

let me explain my situation . I have a table which store the month and date of an entity . Now i want to find out the entities between two dates . I know between can be done only in date ,so i am formating the value into date saying date(MONTH || '-' || '01'|| '-' || YEAR) ,here year and month will be provided by user.
And moreover i have to execute this query using Hibernate Criteria .
I hope u understood the intent .Pls help me out ..
Jomin

The dash ('-') is typically used to separate year/month/day if the date is represented as ISO date. But then, the order would be YEAR-MONTH-DAY (which is the only sensible way to represent dates anyway ).

When constructing dates this way, also be sure that you have leading 0s in the year/month portion. Otherwise, you would not get a valid date value and DB2 will rightfully complain.

You should use this:

select * from from user where <DATE_COLUMN> between '2005-01-01' and '2009-01-01'

Anything else is unnecessary and will perform very poorly (especially if their is an index on your DATE_COLUMN).