JPQL Query Doubts

[ranjisona]

For Example,

IN SQL- to convert DATETIME FIELD to String, we use to_char(s.startTime,‘dd/mm/yyyy’) but in JPQL how can we convert or any other format to take ‘dd/mm/yyyy’ format from DATETIME FIELD.

IN JPQL to ROUND OFF Decimal Points what function is used

[krivopustov (Konstantin Krivopustov)]

Use FUNCTION operator to invoke database functions. For example:

select function('to_char', u.createTs, 'DD/MM/YYYY') from sec$User u

Also, it’s a very rare case when you need such transformations in CUBA applications. What is your use case?

[ranjisona]

Its work fine.

Thanks

[ranjisona]

select
function(‘to_char’,w1.startTime,‘dd/mm/yyyy’),(sum(w1.duration_in_milli_second)/3600000) exe_count from worklog$WorkLog w1
where @between(w1.startTime,now-7,now,day,user_timezone)
group by w1.startTime
order by w1.startTime

If I run this query, I get result as
20/08/2018 0
20/08/2018 0
21/08/2018 0
21/08/2018 0
21/08/2018 0
21/08/2018 0
But I want result as
20/08/2018 0
21/08/2018 0