Hibernate (HQL) date function issue

Posted on 2012-09-11
Last Modified: 2012-10-02
I am trying to query some data from DB2 database by using HQL. Basically I am trying get previous day records.
The following where condition is working fine when i exceute in data studio (DB2 client/IDE).

In database CREATE_TS column was defined as TIMESTAMP.

But the same thing is not working with HQL. i tried several ways, i am getting SQLCODE=-182, SQLSTATE=42816 (Explanation: A date, time, or timestamp expression not valid).

Can anyone help me with this please ?

Thanks in Advance.
Question by:kcmuthyala2
    LVL 23

    Expert Comment

    Can you post hql query you are using here?

    Accepted Solution

    I found solition for this.
    HQL ..... where (abc.create_ts >= :startTime  and abc.create_ts <= :endTime )

            Calendar cal = Calendar.getInstance();          
            // Subtract 1 days from the calendar        
            cal.add(Calendar.DATE, -1);        
            cal.set(Calendar.HOUR_OF_DAY, 0);
            cal.set(Calendar.MINUTE, 0);
            cal.set(Calendar.SECOND, 0);
            Date startTime = cal.getTime();
            cal.set(Calendar.HOUR_OF_DAY, 23);
            cal.set(Calendar.MINUTE, 59);
            cal.set(Calendar.SECOND, 59);
            Date endTime = cal.getTime();

    Author Comment

    I would like to give 400 ponits for my solution.

    Author Closing Comment

    I waited, i did not get answer intime from others, meanwhile i found solution.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
    Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
    This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now