Here is the timestamp, that i was sending for fiveDays to the query.
2011-04-30 17:30:12.323
I am using Oracle 11g and the openJPA, which is by dafualt in RAD 7.5
SysDate i can't really use that since i am using Named Query.
Here is my named Query
@NamedQuery(name = "getXXXXByUser", query = "select cust from ECustomer cust
inner join cust.eCustXXXXXCollection emailAddr inner join
cust.myXXXXXXXCollection mb inner join mb.myXXXXXXCollection tod where
LOWER(emailAddr.addres) =LOWER(:email) and tod.todDprtDttm > :fivedays"),
ECustomer is parent table and its having relationships with other tables as one
to many
I want to use customer object, so that i can populate, whatever i
want from the db.
--- On Fri, 6/5/11, Michael Dick <[email protected]> wrote:
> From: Michael Dick <[email protected]>
> Subject: Re: SysDate-5 days implmentation
> To: [email protected]
> Date: Friday, 6 May, 2011, 6:33 AM
> Your query should work, assuming the
> value for the fivedays is of the
> appropriate type (java.sql.Timestamp from what you've
> said).
>
> If you could post your entities, and table definition to
> pastebin or
> something similar that might help us. Also, which version
> of OpenJPA and
> which database are you using?
>
> Failing that you could use a native query and rely on the
> database
> (presumably Oracle) to handle the sysdate keyword.
>
> -mike
>
> On Thu, May 5, 2011 at 5:47 PM, <[email protected]>
> wrote:
>
> > Yes i tries ld that one. But its still diplays older
> than 5 days.My context
> > is something different. I don't want to use the
> range b/w two dates
> > Here is my scenario, I want
> to retrieve all the the ticket bookings
> > which are starts from currentdate-5 and
> Bookings can be any date in
> > future.
> > As I said previously, in db
> schema the type is date but in jpa entities
> > it's timestamp. Since date in SQL can store the time
> format like 2011-04-12
> > 0:12:23 so we have kept in as date in db schema
> and to store in this format
> > we are using @temporal.type timestamp we r using
> >
> >
> > > Query q = em.createQuery("Select m from Message m
> "
> > > + "where m.targetTime >
> :fivedays
> >
> >
> > Sent from my iPhone
> >
> > On May 5, 2011, at 6:19 PM, Rick Curtis <[email protected]>
> wrote:
> >
> > > Did you try Mike's answer?
> > >
> > > Date now = new Date();
> > > Date thirtyDaysAgo = new Date(now.getTime() - (30
> * MS_IN_DAY));
> > >
> > > Query q = em.createQuery("Select m from Message m
> "
> > > + "where m.targetTime < :now and
> m.targetTime > :thirtyDays");
> > > q.setParameter("now", now);
> > > q.setParameter("thirtyDays", thirtyDaysAgo);
> > >
> > > List<Message> results =
> (List<Message>) q.getResultList();
> > >
> > >
> > >
> > > On Thu, May 5, 2011 at 4:35 PM, ram <[email protected]>
> wrote:
> > >
> > >> Hi,
> > >> I am trying to implement sysdate-5 in
> jpa.
> > >>
> > >> select *From emp where joinDate >
> sysdate-5
> > >>
> > >> The problem is
> in db schema, join date is defined as date, but in
> > >> jpa entites, we have made it as TimeStamp by
> using @temporal so the it
> > can
> > >> store exact timestamp.
> > >>
> > >> when i tried to query
> by using @namedQuery, i am getting older than
> > 5
> > >> days. But i need data which is just 5 days
> old.
> > >> I followed the following
> link
> > >>
> > >>
> > http://stackoverflow.com/questions/2539035/how-to-do-a-timestamp-comparison-with-jpa-query
> > >>
> > >> but still i am getting same
> problem. Any ideas on this.
> > >>
> > >>
> > >> Thanks
> > >>
> > >>
> > >>
> >
>
