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'

Solution

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")));

Comments

[user3488146]

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

[rossa]

Can you use native query that makes to_char(tn.dbdate,'yyyy-MM-dd')?

[user3488146]

yes i can but that is not the solution.there is need of solution in JPA criteria or JPQL

[user3488146]

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').

[Mikko Maunu]

Right, cb.literal should be used to have format as Expression<String>.

[user3488146]

Thanks alot ... cb.literal() solves my problem. its working now :)

[Sanjay Salunkhe]

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.SingularAttributePat‌​h,org.hibernate.ejb. criteria.expression.LiteralExpression) on Java::OrgHibernateEjbCriteria::Criteri aBuilderImpl

[Milesh]

Instead of "yyyy-MM-dd" try "%Y-%m-%d". Refer stackoverflow.com/questions/33153144/…