As stated in the title, I'm facing this issue when optional paramter(endDate) is null. It doesn't throw any error when both parameters are provided. I tried usin cast null as timestamp with time zone, cast as timestamp to both params in the query and it throws the same error. Please advise.

@Query("""
SELECT setspanyId, COUNT(sets)
FROM WarehouseSetsEntity sets
WHERE (:endDate IS NULL AND sets.importDate >= :beginDate)
OR (:endDate  IS NOT NULL AND sets.importDate BETWEEN :beginDate  AND :endDate)
GROUP BY setspanyId""")
List<Object[]> fetchCompanyByDateRange(@Param("beginDate")  OffsetDateTime beginDate,  @ Param("endDate") @NullableOffsetDateTime endDate);

Error:.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select wse1_0panyid,count(wse1_0.setid) from sets wse1_0 where (coalesce(?,'1970-01-01T00 00:00Z') is null and wse1_0.import_date>=?) or (? is not null and wse1_0.import_date between ? and ?) group by wse1_0panyid] [ERROR: could not determine data type of parameter $3] [n/a]; SQL [n/a]

As stated in the title, I'm facing this issue when optional paramter(endDate) is null. It doesn't throw any error when both parameters are provided. I tried usin cast null as timestamp with time zone, cast as timestamp to both params in the query and it throws the same error. Please advise.

@Query("""
SELECT setspanyId, COUNT(sets)
FROM WarehouseSetsEntity sets
WHERE (:endDate IS NULL AND sets.importDate >= :beginDate)
OR (:endDate  IS NOT NULL AND sets.importDate BETWEEN :beginDate  AND :endDate)
GROUP BY setspanyId""")
List<Object[]> fetchCompanyByDateRange(@Param("beginDate")  OffsetDateTime beginDate,  @ Param("endDate") @NullableOffsetDateTime endDate);

Error:.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select wse1_0panyid,count(wse1_0.setid) from sets wse1_0 where (coalesce(?,'1970-01-01T00 00:00Z') is null and wse1_0.import_date>=?) or (? is not null and wse1_0.import_date between ? and ?) group by wse1_0panyid] [ERROR: could not determine data type of parameter $3] [n/a]; SQL [n/a]

• java
• sql
• spring
• spring-boot
• jpa

Share Follow edited Mar 10 at 23:53 ilaunchpad asked Mar 10 at 23:44 ilaunchpadilaunchpad 1,33333 gold badges1717 silver badges3232 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Can you try using below code?

WHERE sets.importDate >= :beginDate AND (sets.importDate <= :endDate OR :endDate IS NULL)