Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Cool Unanswered: me getting this DB error

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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.

  3. #3
    Join Date
    Jan 2008
    Posts
    2
    Quote Originally Posted by jsharon1248
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •