JPA Criteria API: how to retrieve date in "mm/dd/yyyy" format

Question

I want to retrieve one of my column date in "mm/dd/yyyy" format. This column is currently returning date and time both but i want only date in "mm/dd/yyy" format.

Below is my postgresql query that i want to convert to criteria api

select    DISTINCT c.name as Facility,
          to_char(begin_exam,'mm/dd/yyyy') as begin_exam
from  a inner join  b on a.rad_exam_id = b.id 
                      inner join  c on c.id = b.site_id
group by c.name,to_char(begin_exam,'mm/dd/yyyy')
order by c.name,to_char(begin_exam,'mm/dd/yyyy')

I searched on the internet a lot but did't find any solution that will help me. please help me in writing criteria api query for this.

You could extract it as a normal `java.util.Date`, then format it in your HTML/XML/JSON output or whatever you're using. — coladict, Oct 13 '16 at 10:39

ujulu · Answer 1 · 2016-10-13T20:18:13.243

Criteria API defines function expression to execute native SQL functions in the CriteriaBuilder interface as follows:

<T> Expression<T> function(String name, Class<T> type, Expression<?>... args);

where name is the name of the SQL function, type is the expected return type and args is a variable list of arguments (if any).

Here is an example how to use it in a Criteria query:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(String.class);
Root<RadExamTimes> root = cq.from(RadExamTimes.class);
cq.select( cb.function("to_char", String.class, root.get("begin_exam"), cb.literal("MM/DD/YYYY")));

TypedQuery<String> query = entityManager.createQuery(cq);
List<String> result = query.getResultList();

where

RadExamTimes: a hypothetical root entity
MM/DD/YYYY: a database-specific format (in this example Postgresql date format; for Oracle use Ora format, etc)
to_char: Postgresql function to convert date value to string
begin_exam: the date field to be formatted

The format string cannot be passed as is so that the literal() method is used to wrap it.

Note: The above example is tested on MySQL database with MySQL function and corresponding date format; but the example changed to match Postgresql syntax.

Thanks for your answer. but i am in jruby (java + ruby) technology . it is giving me error as NameError: no method 'function' for arguments (org.jruby.RubyString,org.jruby.Ru byClass,org.hibernate.ejb.criteria.expression.LiteralExpression) on Java::OrgHib ernateEjbCriteria::CriteriaBuilderImpl — Sanjay Salunkhe, Oct 13 '16 at 02:54
Thx for the feedback. Shouldn't you tag your question accordingly? JRuby is not mentioned. — ujulu, Oct 13 '16 at 06:05
I just tested on my system to verify that it really works and modified the answer as it might help other users with similar problem. — ujulu, Oct 13 '16 at 20:19

score 0 · Answer 2 · edited Oct 12 '16 at 20:18

0

SELECT '2001-02-16 20:38:40'::date;

date
----------------
2001-02-16
(1 row)

Or you can use @TemporalType on JPA entity field.

edited Oct 12 '16 at 20:18

dur

15,689
25
79
125

answered Oct 12 '16 at 20:14

Viplove Gujrathi

13
8

i want the date in mm/dd/yyyy format and where to put @temporatType keyword in criteria api query – Sanjay Salunkhe Oct 12 '16 at 20:25

score 0 · Answer 3 · answered Dec 18 '19 at 07:31

0

If you want to display the column date in "mm/dd/yyyy" format for the sake of displaying it in the front end. Following might work on this case.

public class SomeDTO {

@JsonFormat(pattern="mm/dd/yyyy")
private Date someDate;

}

answered Dec 18 '19 at 07:31

greenhorn

594
6
19

JPA Criteria API: how to retrieve date in "mm/dd/yyyy" format

3 Answers3

Linked