CODEWITHSUNDEEP
jpqlformatdatetime
Marek Borecki
Marek Borecki

Reputation: 440

how to format date in JPQL?

I want to aggregate payments by date. How to format LocalDateTime in JPQL? I don't want to use nativeQuery.

@Query(value = "select new com.example.demoreconciliation.model.Test(" +
        "    s.paymentDate.format(DateTimeFormatter.ofPattern('yyyy-MM-dd')) as day, " +
        "    count(s.paymentAccountId), " +
        "    sum(s.amount) " +
        ") from AccountStatement s " +
        "GROUP BY day")
List<Test> test();

Upvotes: 2

Views: 185

Answers (1)

Marek Borecki
Marek Borecki

Reputation: 440

I found solution itself. First I had to cast date into string by CAST and then I could cut string by SUBSTRING.

SUBSTRING(CAST(s.paymentDate AS string), 0, 11)

Upvotes: 2

Related Questions