how to select rows based on date range when sometimes the date range might have null values (select all rows)
I have a table called Project(id, name, certification_date); id is int, name is varchar(250) and certification_date is timestamp.
When a project is inserted then, only id and name is inserted, but certification_date is null. Later the user can certify the project then the row is updated with certification_date with the date the user certifies the project. So in project table i can have some rows with certification_date as null and some with a date (ex. 2020-06-18 00:12:07)
Now I have a search query with name and has a filter of startDate and endDate to filter certification_date. below is the Repository query with full signature.
@Query(value = "select distinct p.id, p.name, p.certification_date from project p WHERE (LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') and ((cast(:startDate as timestamp) is null or p.CERTIFICATION_DATE >= :startDate) AND (cast(:endDate as timestamp) is null or p.CERTIFICATION_DATE < :endDate ))", nativeQuery = true)
public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);
So when searchProjects() executes with all the values, it returns the results correctly. But when i need no date filters, i.e startDate and endDate is null, it fails. (here we dont have any issue with searchKey because if searchKey is null I pass "" so it returns all values)
The Exception is
org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to timestamp without time zone
So what do i need to do to make the startDate and endDate optional, so that if those dates are not passed just return all(whether certification_date is null or has some values) rows ?
I have also tried many solutions from Stackoverflow but they don't work for me.
@Query(value = "select distinct p.id, p.name, p.certification_date from project p " +
"where LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') " +
"and (cast(cast(:startDate as text) as timestamp) is null or p.CERTIFICATION_DATE >= :startDate) " +
"and (cast(cast(:endDate as text) as timestamp) is null or p.CERTIFICATION_DATE < :endDate)", nativeQuery = true)
public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);
class Employee {
int id;
Date birthDate;
}
List<Employee> findByBirthDateNotNullAndBirthDateLessThanEqualAndBirthDateGreaterThanEqual(Date startDate,Date endDate)