I got a syntax error when trying to call JPA function

Question

When Im trying to call JPA function this statement I got an error: syntax error at or near ":"

 public interface BcaTestRepository extends CrudRepository<InBodyBCA, Long> {
  @Query(value = "SELECT * FROM in_body_bca bca WHERE person_id = :personId " +
      "AND to_timestamp(bca.datetimes::text, 'YYYYMMDDHH24MISS')  BETWEEN :startRange AND :endRange",
      nativeQuery = true)
  List<InBodyBCA> findAllByPersonId(@Param("personId") Long personId,
                                    @Param("startRange") LocalDateTime startRange,
                                    @Param("endRange") LocalDateTime endRange);

But in PgAdmin the query works fine

SELECT id, to_timestamp(datetimes::text, 'YYYYMMDDHH24MISS') as dt FROM in_body_bca WHERE to_date(datetimes::text, 'YYYYMMDDHH24MISS')
BETWEEN '2018-05-07' AND '2019-05-07' ORDER BY to_date(datetimes::text, 'YYYYMMDDHH24MISS') DESC ;

What data type is bca.datetimes? Casting to text combined with to_timestamp() doesn't really make sense. What are you trying to do there? — user330315
– user330315, Commented Jul 11, 2019 at 9:47

Maciej Kowalski · Accepted Answer · 2019-07-11 09:38:46Z

4

You use the double colon here: bca.datetimes::text. JPA would look for text variable name.

You need to escape it:

bca.datetimes\\:\\:text

answered Jul 11, 2019 at 9:38

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3693537 Over a year ago

Sorry! It actually did help! Thank you!

user330315 Over a year ago

@user3693537: Alternatively: cast(bca.datetimes as text)

Collectives™ on Stack Overflow

I got a syntax error when trying to call JPA function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related