Jpa Query Month/Year Functions

I want to query YEAR(itemDate) but the function doesn't exit, then i saw the SUBSTRING() function so what i did was Select q from table where SUBSTRING(itemDate, 1, 4)='2011' and it works for me! hope it helps!

if you need you a dynamic variable, you can do that too. here :poDate is the year which is deifned in the setParameter();

@NamedQuery(name = "PurchaseOrders.findByYear", query = "SELECT p FROM PurchaseOrders p WHERE SUBSTRING(p.poDate, 1, 4) = :poDate")

Query q = em.createNamedQuery("PurchaseOrders.findByYear");
q.setParameter("poDate", s_year+"");

but if your okay with your solutions, that'll be fine. i just find JPA faster to execute.

The MONTH() function exists in Hibernate HQL but is not a standard JPA function. Maybe your JPA provider has some proprietary equivalent but you didn't mention it. If it doesn't, fall back on native SQL.

I am using Toplink Essentials for the same. Please help, if any function exists in Toplink. Thanks.

To my knowledge, TopLink doesn't have a direct equivalent. So either use a native SQL query or maybe a TopLink Expression query (not sure about this, and not sure this is available in TopLink Essentials).

Following worked for me with hibernate (4.3.9.Final) & JPA 2.1.

@NamedQuery(name = "PartyEntity.findByAID", query = "select distinct psc.party from PartyShortCode psc where (psc.shortCode = :aidNumber or FUNCTION('REPLACE',psc.accountReference,' ','') = :aidNumber) and psc.sourceSystem in :sourceSystem")

if your class holds a date type variable, you can use a query like this:

@Query("select m from Movement m where m.id_movement_type.id=1 and SubString(cast(m.date as text),1,4) = :year")
    List<Movement> buysForYear(@Param("year") String year);