title: Search records between two dates using Spring Data JPA native query?
body: <p>I am trying to create an endpoint with Spring Data Jpa to enable users/client to find details of records using the date column from my table, however the records that are being fetched are only records from before the end date.</p>
  <p>For example, I want records from between 18th - 20th, but the records that are fetched are records from 18th &amp; 19th leaving out the records for 20th even though records for that date (20th) exists in the table. I need a way to retrieve the records of 18th and 19th along with that pertaining to the end date which is 20th in this case. Any help is appreciated.</p>
  <p>Below are the approaches I have tried in my JPA repository using the JPA native query.</p>
  <p>In the first approach, I used the native query BETWEEN but the records from the end date are not being fetched. This is the code snippet below:</p>
  <pre class="lang-java prettyprint-override"><code>@Query(value = &quot;SELECT * FROM PAYMENT_MASTER WHERE LAST_UPDATED BETWEEN :startDate AND :endDate&quot;, nativeQuery = true)
  List&lt;PaymentMaster&gt; getAllBetweenDates(@Param(&quot;startDate&quot;) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate startDate,
                                         @Param(&quot;endDate&quot;) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate endDate);
  </code></pre>
  <p>Below is another approach I tried that gave me the same results as described above and as the previous approach. Here I used the native query GREATER THAN/EQUAL TO AND LESS THAN/EQUAL TO but still didn't get the desired outcome. Here's the code snippet from the repository:</p>
  <pre class="lang-java prettyprint-override"><code>@Query(value = &quot;SELECT * FROM PAYMENT_MASTER WHERE LAST_UPDATED &gt;= :startDate AND LAST_UPDATED &lt;= :endDate&quot;, nativeQuery = true)
  List&lt;PaymentMaster&gt; getAllBetweenDates(@Param(&quot;startDate&quot;) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate startDate,
                                         @Param(&quot;endDate&quot;) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDate endDate);
  </code></pre>
  <p>Here is a code snippet from my controller:</p>
  <pre class="lang-java prettyprint-override"><code>@PostMapping(&quot;/find/date-between&quot;)
  public ResponseEntity&lt;Object&gt; findPaymentByDate(@RequestBody DateSearcherDto searcherDto) {
      List&lt;PaymentMaster&gt; paymentMasterList = paymentMasterRepository.getAllBetweenDates(searcherDto.getStartDate(), searcherDto.getEndDate());
      List&lt;PaymentMasterDto&gt; resultsDto = new ArrayList&lt;&gt;();
  }
  </code></pre>
  <p><strong>UPDATE</strong></p>
  <p>Please check out my DateSearcher DTO whicd I created below.</p>
  <pre><code>
  package com.oasis.firsbacklogbackend.dto;
  
  import lombok.Data;
  import org.springframework.format.annotation.DateTimeFormat;
  
  import java.time.LocalDateTime;
  
  @Data
  public class DateSearcherDto {
  
      @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = &quot;yyyy-MM-dd&quot;)
      private LocalDateTime startDate;
  
      @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME, pattern = &quot;yyyy-MM-dd&quot;)
      private LocalDateTime endDate;
  
  }
  
  
  </code></pre>
  <p>Please your help would be appreciated. Thanks.</p>