any pointers or thoughts on this. Thanks
--- On Fri, 6/5/11, ram <[email protected]> wrote: > From: ram <[email protected]> > Subject: Re: SysDate-5 days implmentation > To: [email protected] > Cc: [email protected] > Date: Friday, 6 May, 2011, 8:20 AM > 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 > > > >> > > > >> > > > >> > > > > > >
