select specific date format in jpa criteria query

Question

I want to select date in particular format in multiselect of jpa criteria query like we use

select to_char(tn.dbdate,'yyyy-MM-dd') from transaction tn

in oracle.

I am able to use

query.multiselect(cb.function("TO_CHAR",String.class,transaction.get("dbdate")) );

but this returns date in database format i.e.

Wed Apr 2 12:20:50 2014

but how to get this in specific date format

'yyyy-MM-dd'

Mikko Maunu · Accepted Answer · 2014-04-03T17:22:33.583

12

In current query no format is given as an argument to TO_CHAR function. That's why it cannot do much else than fall back to default. As documented, more than one arguments can also be passed to database function via CriteriaBuilder.function:

query.multiselect(
    cb.function("TO_CHAR",
                String.class,transaction.get("dbdate"), 
                cb.literal("yyyy-MM-dd")));

edited Apr 03 '14 at 17:22

answered Apr 02 '14 at 17:57

Mikko Maunu

41,366
10
132
135

thanks but I had tried this.its showing compilation error for cb.function() method.There should be some expression instead of string at place ('yyyy-MM-dd'). – user3488146 Apr 03 '14 at 04:40
Right, cb.literal should be used to have format as Expression. – Mikko Maunu Apr 03 '14 at 17:23
Thanks alot ... cb.literal() solves my problem. its working now :) – user3488146 Apr 04 '14 at 05:13
Not working for me ..getting error as NameError: no method 'function' for arguments (org.jruby.RubyString,org.jruby.Ru byClass,org.hibernate.ejb.criteria.path.SingularAttributePath,org.hibernate.ejb. criteria.expression.LiteralExpression) on Java::OrgHibernateEjbCriteria::Criteri aBuilderImpl – Sanjay Salunkhe Oct 12 '16 at 20:32
Instead of "yyyy-MM-dd" try "%Y-%m-%d". Refer http://stackoverflow.com/questions/33153144/how-to-search-date-field-for-a-string-using-jpa-criteria-api – Milesh Nov 10 '16 at 10:43

score 0 · Answer 2 · answered Apr 02 '14 at 07:40

0

You can take the date from DB and format it as you want later.

answered Apr 02 '14 at 07:40

rossa

119
5

No, I am not using custom classes i.e. i need to show directly as fetched from database. So need to format in query only – user3488146 Apr 02 '14 at 09:05
Can you use native query that makes to_char(tn.dbdate,'yyyy-MM-dd')? – rossa Apr 02 '14 at 11:13
yes i can but that is not the solution.there is need of solution in JPA criteria or JPQL – user3488146 Apr 02 '14 at 11:43

score 0 · Answer 3 · edited Aug 28 '18 at 03:34

0

This worked for me:

Expression<String> dateStringExpr = cb.function("to_char", String.class,
        root.get("effectiveStartDate"), cb.literal("YYYY-MM-DD HH12:MI:SS"));

ls.add(cb.like(cb.lower(dateStringExpr), "%" + effectiveStartDate.get().toLowerCase() + "%"));

edited Aug 28 '18 at 03:34

Patrick Mevzek

10,995
16
38
54

answered Aug 28 '18 at 03:25

Sheri Bgh

41
1

select specific date format in jpa criteria query

3 Answers3

Linked