title: How to use springframework.data.jpa.repository.Query to find all results between two dates ignoring time
body: <p>I am struggling with such a simple task and I have no idea what I am doing wrong.</p>
  <p>Based on personal research, it seems I am coding what I am supposed to in order to search all records between two dates using Spring Data. As far as I understood, &quot;@Temporal(TemporalType.DATE)&quot; does the &quot;magic&quot;.</p>
  <p>Repository</p>
  <pre><code>import java.math.BigDecimal;
  import java.util.Date;
  import java.util.List;
  
  import javax.persistence.TemporalType;
  
  import org.springframework.data.jpa.repository.JpaRepository;
  import org.springframework.data.jpa.repository.Query;
  import org.springframework.data.jpa.repository.Temporal;
  import org.springframework.data.repository.query.Param;
  import org.springframework.stereotype.Repository;
  
  import com.mybank.accountmanagement.model.Transaction;
  
      @Repository
      public interface TransactionRepository extends JpaRepository&lt;Transaction, Long&gt; {
      
          
  @Query(value = &quot;SELECT * FROM transactions WHERE account_id=:idAccount AND CAST(created_at AS date) BETWEEN :fromDate AND :toDate &quot;, nativeQuery = true)
  List&lt;Transaction&gt; findByAccountIdWithCreationDateBetween(@Param(&quot;idAccount&quot;) Long idAccount,
          @Param(&quot;fromDate&quot;) @Temporal(TemporalType.DATE) Date fromDate,
          @Param(&quot;toDate&quot;) @Temporal(TemporalType.DATE) Date toDate);
          
      }
  </code></pre>
  <p>Models:</p>
  <p>Transaction</p>
  <pre><code>package com.mybank.accountmanagement.model;
  
  import java.math.BigDecimal;
  
  import javax.persistence.Entity;
  import javax.persistence.FetchType;
  import javax.persistence.GeneratedValue;
  import javax.persistence.GenerationType;
  import javax.persistence.Id;
  import javax.persistence.JoinColumn;
  import javax.persistence.ManyToOne;
  import javax.persistence.Table;
  import javax.validation.constraints.NotNull;
  
  import org.hibernate.annotations.OnDelete;
  import org.hibernate.annotations.OnDeleteAction;
  import org.springframework.format.annotation.NumberFormat;
  
  import com.fasterxml.jackson.annotation.JsonIdentityInfo;
  import com.fasterxml.jackson.annotation.JsonIdentityReference;
  import com.fasterxml.jackson.annotation.JsonProperty;
  import com.fasterxml.jackson.annotation.ObjectIdGenerators;
  
  @Entity
  @Table(name = &quot;transactions&quot;)
  public class Transaction  extends AuditModel {
  
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
  
      @ManyToOne(fetch = FetchType.LAZY, optional = false)
      @JoinColumn(name = &quot;account_id&quot;, nullable = false)
      @OnDelete(action = OnDeleteAction.CASCADE)
      @JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = &quot;id&quot;)
      @JsonIdentityReference(alwaysAsId = true)
      @JsonProperty(&quot;accoout_id&quot;)
      private Account account;
  
      @NotNull
      @NumberFormat(pattern = &quot;#,###,###,###.##&quot;)
      private BigDecimal amount;
  
      @NotNull
      private int transactionType;
  
      
      
      
      public Transaction(Account account, @NotNull BigDecimal amount, @NotNull int transactionType) {
          super();
          this.account = account;
          this.amount = amount;
          this.transactionType = transactionType;
      }
  
      public Long getId() {
          return id;
      }
  
      public void setId(Long id) {
          this.id = id;
      }
  
      public Account getAccount() {
          return account;
      }
  
      public void setAccount(Account account) {
          this.account = account;
      }
  
      public BigDecimal getAmount() {
          return amount;
      }
  
      public void setAmount(BigDecimal amount) {
          this.amount = amount;
      }
  
      public int getTransactionType() {
          return transactionType;
      }
  
      public void setTransactionType(int transactionType) {
          this.transactionType = transactionType;
      }
  
      
  }
  </code></pre>
  <p>AuditModel</p>
  <pre><code>package com.mybank.accountmanagement.model;
  
  import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
  import org.springframework.data.annotation.CreatedDate;
  import org.springframework.data.annotation.LastModifiedDate;
  import org.springframework.data.jpa.domain.support.AuditingEntityListener;
  
  import javax.persistence.*;
  import java.io.Serializable;
  import java.util.Date;
  
  @MappedSuperclass
  @EntityListeners(AuditingEntityListener.class)
  @JsonIgnoreProperties(value = { &quot;createdAt&quot;, &quot;updatedAt&quot; }, allowGetters = true)
  public abstract class AuditModel implements Serializable {
      @Temporal(TemporalType.TIMESTAMP)
      @Column(name = &quot;created_at&quot;, nullable = false, updatable = false)
      @CreatedDate
      private Date createdAt;
  
      @Temporal(TemporalType.TIMESTAMP)
      @Column(name = &quot;updated_at&quot;, nullable = false)
      @LastModifiedDate
      private Date updatedAt;
  
      public Date getCreatedAt() {
          return createdAt;
      }
  
      public void setCreatedAt(Date createdAt) {
          this.createdAt = createdAt;
      }
  
      public Date getUpdatedAt() {
          return updatedAt;
      }
  
      public void setUpdatedAt(Date updatedAt) {
          this.updatedAt = updatedAt;
      }
  }
  </code></pre>
  <p>What I was expcting to return when searching with &quot;fromDate&quot;:&quot;2020-08-02&quot;, &quot;toDate&quot;:&quot;2020-08-02&quot;</p>
  <p><a href="https://i.stack.imgur.com/Nx6c3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Nx6c3.png" alt="postgres admin" /></a></p>
  <p>But it is returning no record at all</p>
  <p>*** Edited</p>
  <p>For some unknown reason for me, my records with exact date matching fromDate and toDate are token off from result</p>
  <ol>
  <li><p>Searching with longer fromDate and toDate</p>
  <p>curl --location --request GET 'localhost:2000/transaction/1/bankstatement' --header 'Content-Type: application/json' --data-raw '{
  &quot;fromDate&quot;:&quot;2020-08-01&quot;,
  &quot;toDate&quot;:&quot;2020-08-03&quot;
  }'</p>
  </li>
  </ol>
  <p>brings two records:</p>
  <pre><code>[
      {
          &quot;createdAt&quot;: &quot;2020-08-02T16:29:08.085+00:00&quot;,
          &quot;updatedAt&quot;: &quot;2020-08-02T16:29:08.085+00:00&quot;,
          &quot;id&quot;: 1,
          &quot;amount&quot;: 1.00,
          &quot;transactionType&quot;: 2,
          &quot;accoout_id&quot;: 1
      },
      {
          &quot;createdAt&quot;: &quot;2020-08-02T16:29:11.185+00:00&quot;,
          &quot;updatedAt&quot;: &quot;2020-08-02T16:29:11.185+00:00&quot;,
          &quot;id&quot;: 2,
          &quot;amount&quot;: 2.00,
          &quot;transactionType&quot;: 1,
          &quot;accoout_id&quot;: 1
      }
  ]
  </code></pre>
  <p>so far so good</p>
  <ol start="2">
  <li><p>now I get a problem while searching with exact date. I would expect same result from above query</p>
  <p>curl --location --request GET 'localhost:2000/transaction/1/bankstatement' --header 'Content-Type: application/json' --data-raw '{
  &quot;fromDate&quot;:&quot;2020-08-02&quot;,
  &quot;toDate&quot;:&quot;2020-08-02&quot;
  }'</p>
  </li>
  </ol>
  <p>brings no result at all</p>
  <p><a href="https://i.stack.imgur.com/o08iC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/o08iC.png" alt="no results" /></a></p>
  <p>*** edited. Thanks to sample provided by Kavithakaran Kanapathippillai, I noticed  that it works with new Date or 2020-08-02T00:00:00.000+00:00 but it fails with 2020-08-02. I am still lost why it fails with 2020-08-02 since I don't care about time and also it works if try one day less: 2020-08-01</p>
  <p>Controller</p>
  <pre><code>import java.math.BigDecimal;
  import java.util.Date;
  import java.util.List;
  
  import javax.validation.Valid;
  
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.web.bind.annotation.GetMapping;
  import org.springframework.web.bind.annotation.PathVariable;
  import org.springframework.web.bind.annotation.PostMapping;
  import org.springframework.web.bind.annotation.RequestBody;
  import org.springframework.web.bind.annotation.RestController;
  
  import com.mybank.accountmanagement.BankStatementFilter;
  
  import com.mybank.accountmanagement.model.Transaction;
  import com.mybank.accountmanagement.repository.TransactionRepository;
  import com.mybank.accountmanagement.service.TransactionService;
  
  @RestController
  public class TransactionController {
  
      @Autowired
      TransactionService transactionService;
  
      @Autowired
      TransactionRepository transactionRepository;
  
      @GetMapping(&quot;/transaction/{accountId}/bankstatement&quot;)
      public List&lt;Transaction&gt; bankStatement(@PathVariable(value = &quot;accountId&quot;) Long accountId,
              @Valid @RequestBody BankStatementFilter bankStatementFilter) {
  
          return transactionRepository.findByAccountIdWithCreationDateBetween(accountId,
                  bankStatementFilter.getFromDate(), bankStatementFilter.getToDate());
      }
  
  }
  </code></pre>
  <p>A simple Pojo used only to get new Date from the string sent by client (ex. Postman or curl)</p>
  <pre><code>import java.util.Date;
  
  public class BankStatementFilter {
      
      private Date fromDate;
      private Date toDate;
      public Date getFromDate() {
          return fromDate;
      }
      public void setFromDate(Date fromDate) {
          this.fromDate = fromDate;
      }
      public Date getToDate() {
          return toDate;
      }
      public void setToDate(Date toDate) {
          this.toDate = toDate;
      }
  
  }
  </code></pre>
  <p>It smells some stupid thing done from my part with this POJO BankStatementFilter</p>
  <p>Here is what I noted while comparing new Date() with 2020-08-02. Since I don't care about time, isn't that correctly?</p>
  <p><a href="https://i.stack.imgur.com/2560w.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2560w.png" alt="new time versus 2020-08-02" /></a></p>
  <p>*** Final comment</p>
  <p>It is working now. I changed my POJO to bellow. If I am doing some silly thing I will be thanks for advice. BTW, my original question was 100% answered.</p>
  <pre><code>import java.text.ParseException;
  import java.text.SimpleDateFormat;
  import java.util.Date;
  
  public class BankStatementFilter {
  
      private Date fromDate;
      private Date toDate;
  
      public Date getFromDate() {
          return fromDate;
      }
  
      public void setFromDate(String fromDate) {
  
          String pattern = &quot;yyyy-MM-dd&quot;;
          SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
  
          try {
              this.fromDate = simpleDateFormat.parse(fromDate);
          } catch (ParseException e) {
              e.printStackTrace();
          }
      }
  
      public Date getToDate() {
          return toDate;
      }
  
      public void setToDate(String toDate) {
          String pattern = &quot;yyyy-MM-dd&quot;;
          SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
  
          try {
              this.toDate = simpleDateFormat.parse(toDate);
          } catch (ParseException e) {
              e.printStackTrace();
          }
      }
  
  }
  </code></pre>