How to use date_format when using JPQL/JPA

Question

I am doing Java EE with MySQL as database and implementing JPA on my codes.

I have no problem retrieving the data from MySQL Workbench but when I change my syntax to JPQL's it does not work.

For e.g. in MySQL - it works

SELECT date_format(s.date,'%Y, %m, %d') from Transactions s;

in JPQL - it does not

SELECT date_format(s.date,'%Y, %m, %d') from TransactionEntity s;

How do i modify to suit the JPA query?

Note: in JPQL the following works

SELECT s.date from TransactionEntity s;

Answer 1

SQL function date_format is not part of JPQL, as any documentation would tell you, so don't see the point in just pushing SQL into JPQL and expecting it to work.

What you can do with JPA 2.1 is invoke it as follows

function("date_format", s.date, '%Y, %m, %d')

where function is a way to invoke any native SQL function. This clearly means you lose database independence because that function is not valid on all datastores.