Hello Chris, My entities all contain timestamp fields for the creation date of the record.
as a Test, I created the following: <named-query name="Currency.created"> <query> SELECT c FROM Currency c WHERE c.created <![CDATA[>=]]> :created ORDER BY c.code desc </query> </named-query> @Test public void test() { EntityManagerFactory emf = Persistence.createEntityManagerFactory("g11dev"); EntityManager em = emf.createEntityManager(); SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd"); try { Timestamp date = new Timestamp(df.parse("20100101").getTime()); // This is a java.sql.Timestamp !! TypedQuery<Currency> query = em.createNamedQuery("Currency.created", Currency.class); query.setParameter("created", date); List<Currency> list = query.getResultList(); assertTrue(!list.isEmpty()); } catch (Exception pEx) { pEx.printStackTrace(); fail(); } } The resulting query is: SELECT t0.uniqueid, t0.versionid, t0.active, t0.created, t0.createdby, t0.modified, t0.modifiedby, t0.code, t0.description, t0.includeincustomerlisting, t0.includeinlisting FROM galaxy11.currency t0 WHERE (t0.created >= ?) ORDER BY t0.code DESC [params=(Timestamp) 2010-01-01 00:00:00.0] and works as expected. Hope this helps. Regards, John > -----Original Message----- > From: Chris [mailto:cwolf.a...@gmail.com] > Sent: Thursday, September 12, 2013 7:47 PM > To: users@openjpa.apache.org > Subject: Re: How to compare fields of type TemporalType.TIMESTAMP - but just > by Date? > > In my last post, I asked, "How can I do this in JPA?", but I meant to ask, > "How > can I do this in JPQL?". > > I'm going to guess it's impossible because I'm not seeing any JPQL > date/datetime manipulation functions in the docs, other then CURRENT_DATE | > CURRENT_TIME | CURRENT_TIMESTAMP, so I resorted to a native SQL query > using the syntax shown in my initial post. > > Thanks, > > > Chris > > On 9/12/2013 1:12 PM, Chris wrote: > > I have an entity with a timestamp field, but for the purpose of a > > certain query, I want to only compare by date - and ignore the time > > component of the value. > > > > For example, in Oracle, the raw SQL would look like: > > > > SELECT t0.BASE_DATA_ID, t0.CLIENT_ID, t0.DB_USER, t0.OS_USER, > > t0.UPSERT_DATE, > > t0.CRNCY_CODE, t0.datadate, t0.ident, t0.LAST_PUBLISHED_DATE, > > t0.PRICE_VAL_1, t0.PRICE_VAL_2, t0.TYPE_NAME > > FROM MD_BASE_DATA t0 > > WHERE (t0.CLIENT_ID = 'JUNIT-1' > > and to_date(to_char(t0.UPSERT_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') > > = to_date(to_char(current_date - 1,'YYYY-MM-DD'),'YYYY-MM-DD')) > > > > This query filters by field "UPSERT_DATE" being yesterday, ignoring > > the time part. > > > > How can I do this in JPA? > > > > Thanks, > > > > > > Chris