Named parameter not bound : DATE_FORMAT Native Query in Spring Boot

Question

I am trying to get table data as list using select query between dateFrom and current date using MySQL Native Query in Spring Boot. and MySQL database field datatype is String.

Below is Query in Repository:

@Query(value = "select * from Account where DATETIMESTAMP >= :dateFrom  AND DATETIMESTAMP < :DATE_FORMAT(curdate(), '%d/%m/%Y')", nativeQuery = true)
List<Account> findByDate(@Param("dateFrom") String dateFrom);

Getting below error for above query in Spring Boot:

 Named parameter not bound : DATE_FORMAT; nested exception is org.hibernate.QueryException: Named parameter not bound : DATE_FORMAT

Can anyone please help me to frame the query for the same.

Schroer

Remove : colon before DATE_FORMAT – Exorbitance

Answer 1

Remove the : from :DATE_FORMAT(curdate(), '%d/%m/%Y'). : is used for binding parameters in jpa query. The addition : in front of DATE_FORMAT makes JPA think it as a parameter. So the final query should be

@Query(value = "select * from Account where DATETIMESTAMP >= :dateFrom  AND DATETIMESTAMP < DATE_FORMAT(curdate(), '%d/%m/%Y')", nativeQuery = true)
List<Account> findByDate(@Param("dateFrom") String dateFrom);

Sybil

But its giving empty set. Query looks like select * from Account where DATETIMESTAMP >= "12/01/2020" AND DATETIMESTAMP < DATE_FORMAT(curdate(), '%d/%m/%Y'); and DATETIMESTAMP value in DB is 2020-01-15T06:20:36.793Z. @Sybil – Schroer

I think your DATETIMESTAMP field is VARCHAR and DATETIMESTAMP >= "12/01/2020" is comparing two strings. The comparison will not comapre two dates but will compare two string alphabetically e.g. like "123" will be less than "2". SO it will be preferable if you can convert the time to DateTime. Or else you need to format the dateString like the one you have in db. Since they are same format string comparison will work. Select * from Account where dateT >= "2020-01-01T00:00:00Z" AND dateT < DATE_FORMAT(NOW(), '%Y-%m-%dT%TZ'). Convert the date to 2020-01-01T00:00:00Z format. – Sybil

Answer 2

If you're using JPQL & still getting this error then its possible you might have a space in between like

where id = : myId

Should be

where id = :myId

Garton

Answer 3

In any case, If you face this problem. Before doing debugging or deep google search Check the following things

1. Method variable names and your query variables are matched or not.

2. Query semicolon with your parameters

3. If you renamed the table with short expressions, double-check them they were used correctly.

4. Check your query syntax.

Because most of the issues have come from the above minor mistakes.

Vedda

Thanks, in my case it was your 2: A Spring Data named native query should not be terminated by a semicolon. I got the error Named parameter not bound : myParameter;; (note the double semicolon in the message) which was caused by the query ending with WHERE column = :myParameter;. – Effortful

Answer 4

I had the same error, but in my case I had a comparison operator '<>' joined on to my variable with no space in between. Adding a space between the variable and the operator fixed my issue. ie. changing :addressType<> to :addressType <> worked

Pillion

Answer 5

Usually when you're writing native queries, you may will break lines on your repository code. Like this:

String value = "select from People " + "where id = :id" + "and address = :address"

The error will show: Named parameter not bound :idand

Notice that the repository joined the 2 strings on sequence, and changed the parameter name and reference. You must enter an empty space in the end of each line in order to have a correct semantic

The correct way will be like:

String value = "select from People " + "where id = :id " + "and address = :address"

Ani