Search code examples
jpaeclipselinkjpql

extract year, mounth and day from a date using eclipselink jql

I am sending a date from a primefaces calendar to backing bean. The object that primefaces send is Date. 

<p:calendar id="exact_Date" value="#{h.model.arhExactDate}" pattern="yyyy/MM/dd"/>

The date in debugger looks like Thu Mar 02 00:00:00 EET 2017.

I want to extract from this only the date part (withowt time) and compare it with a timestamp from the database like 2017-03-02 18:57:19.152 using eclipselink jpql.

So it will be something like: cast(2017-03-02 18:57:19.152 as varchar) like 2017-03-02 18 where the part after "like" is the date prat from the date.

Looking at this basic eclipselink guide I see that I can use extract method like: extract(year from my_date). But how can I extract all I need?

I tried the query below but is not a valid expression:

select e from MyEntity e where  extract(year from e.reqDate) = :extract(year from exactDate)

If it worked, I would have liked to have something like:

 extract(year,mounth,day from e.reqDate) =:(exactDate)

I don't want something like: extract(year from e.reqDate) = :extract(year from exactDate) and extract(month from e.reqDate) = :extract(month from exactDate) and extract(day from e.reqDate) = :extract(day from exactDate)

Solution

  • Since you want to pass in a date object and only compare the date values excluding the time portion, you might try using TruncateDate or Trunc options in JPQL:

    "select e from MyEntity e where truncateDate(e.reqDate) = :dateParameter"

    TRUNC is on most databases and if not given a year/month/day paramater should default to stripping out the time. It can be accessed in other providers using JPQL's FUNCTION:

    "select e from MyEntity e where FUNCTION('TRUNC', e.reqDate) = :dateParameter"