4 Replies Latest reply on Feb 10, 2005 8:31 AM by Hideki Mae

    EJB QL and time comparison

    Hideki Mae Newbie

      To all:
      I am trying to do a comparison for a date field. My EJB QL is:
      select object(c) from tableschema as c where start_date between ?1 and ?2

      The dates are: 01/05/2005 00:00AM and 01/05/2005 11:59 PM.
      I am trying to find all data that have a start_date of 1/05/2005.
      My program is using a dynamic query so I need to pass the parameters.
      Before, I passed java.sql.Date but I can not specify the hours and minutes for java.sql.Date.
      Is there a way to do this?

      Thank you.

        • 1. Re: EJB QL and time comparison
          Hideki Mae Newbie

          The EJB QL I posted was incorrect. It should have read:
          select object(c) from tableschema as c where c.start_date between ?1 and ?2

          This is what I have, and I still need a way to pass hours and minute information.

          Thanks for any help you can give me.

          • 2. Re: EJB QL and time comparison
            Dietmar Scheidl Newbie

            Try using java.sql.Timestamp instead of java.sql.Date. AFAIK Date sets the hour, minute, second, ... to 0 to be conform to the standard.

            • 3. Re: EJB QL and time comparison
              jamesstrachan Newbie

              An alternative approach is to strip all the time information from the date before storing the date in the database.

              This works well in commercial applications but may not suit your application.

              James

              • 4. Re: EJB QL and time comparison
                Hideki Mae Newbie

                Thank you for the replies.
                The application that I am working on tries to do a search by the name of the table fieldname and the value. I discovered that when I try to do a search on a date (ex. 01/27/2005), I will get data for 01/27/2005 00:00:00AM. And it did not get data for other times (ex. 01/27/2005 15:59:00 PM). Which is why I needed to pass time stamp information. When data get inserted, the time information must be kept so that we can calculate how long it took for the job to be completed.
                I will try the java.sql.Timestamp.
                Worst comes to worst I think the other option would be to get all the data first and iterate it in a loop, get the date fields and convert it to a string, and compare the string values.

                Thank you for all your help.