I am getting this error

[jominp]

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

[r035198x]

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

That certainly looks wrong. What is the name of the column in the database containing the date?
Have a read at this nice article.

[sakumar9]

The parameter that you are passing to DATE function doesn't seem to be correct:
date(MONTH || '-' || '01'|| '-' || YEAR)
please check again.

Thanks and Regards
----Sanjay

[docdiesel]

Hi,

I assume that MONTH and YEAR are the columns in your table USER. The date is used by DB2 in ISO format by default, means YYYY-MM-DD. So you've got to concatenate the date string the following way:

select
  *
from
  USER
where
  date(  YEAR || '-' || MONTH || '-' || '01'  )
  between '2005-01-01'
  and     '2009-01-01'

Regards,

Bernd