How to format Date in JPA query-CodePudding

I have a SpringBoot application where I use Repository class to query my Oracle DB table.

Here is how the query and associated function are defined :

@Query( value =" SELECT status " 
                   " FROM tb1 "  
                   " WHERE "  
                   " to_date(cob_Date,'dd-MON-yy') = to_date(:cobDate,'yyyy-mm-dd') "  
                   " AND business_Day ='BD3' "  
                   " AND intra_day ='INTRA_06' "  
                   " AND datasource_name =:datasource"  
                   " AND upper(status) = 'COMPLETED' "  
                   " AND frequency = 'MONTHLY' "  
                   " AND processed = 'Y' "  
                   " ORDER BY create_date desc FETCH FIRST 1 rows only"
                   , nativeQuery=true)
    List<String> getImpalaJobStatus(@Param("intraDay") String intraDay,
                                                     @Param("businessDay")  String businessDay,
                                                     @Param("cobDate") LocalDate cobDate,
                                                     @Param("datasource") String datasource);

If I run this query in SQL developer then I am getting my results back, however if I run it from my SpringBoot Application it returns nothing.

I suspect I am doing something wrong with the Date field "COB_DATE" and this clause under WHERE:

" to_date(cob_Date,'dd-MON-yy') = to_date(:cobDate,'yyyy-mm-dd') "

I tried it as :

" cob_Date =:cobDate "

but it didn't work either.

CodePudding user response：

That cobDate is being declared as a LocalDate in the method signature implies that you already have that value in date format. If so, then the call to to_date() in the query is not needed. Try binding the LocalDate value directly:

@Query( value =" SELECT status " 
               " FROM tb1 "  
               " WHERE "  
               " to_date(cob_Date,'dd-MON-yy') = :cobDate "  
               " AND business_Day ='BD3' "  
               " AND intra_day ='INTRA_06' "  
               " AND datasource_name =:datasource"  
               " AND upper(status) = 'COMPLETED' "  
               " AND frequency = 'MONTHLY' "  
               " AND processed = 'Y' "  
               " ORDER BY create_date desc FETCH FIRST 1 rows only"
               , nativeQuery=true)
List<String> getImpalaJobStatus(@Param("intraDay") String intraDay,
                                @Param("businessDay") String businessDay,
                                @Param("cobDate") LocalDate cobDate,
                                @Param("datasource") String datasource);

Note that your Oracle JBDC driver should know how to marshall the LocalDate value to the database such that the query works.