title: JPA LocalDateTime comparison SQL
body: <p>I have a Java project which contains an entity with a LocalDateTime. I want to extract records via SQL where the LocalDateTime falls in between 2 other LocalDateTime's. </p>
  
  <p><strong>ENTITY</strong></p>
  
  <pre><code>@Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(nullable = false)
  private long id;
  
  @Basic(optional = false)
  @Column(nullable = false)   
  private long assignmentId;
  
  @Basic(optional = false)
  @Column(nullable = false)   
  private LocalDateTime startDateTime;
  
  @Basic(optional = false)
  @Column(nullable = false)   
  private LocalDateTime endDateTime;
  </code></pre>
  
  <p><strong>JAVA</strong></p>
  
  <pre><code>//Sub assignments
  public List&lt;Subassignment&gt; findAllSubassignmentsForAssignmentBetweenDates(long id, LocalDateTime startDateTime, LocalDateTime endDateTime) {
      return getEntityManager().createNamedQuery("ResourceVO.findByAssignmentIdAndDates", Subassignment.class)
              .setParameter("id", id)
              .setParameter("sdt", startDateTime)
              .setParameter("edt", endDateTime)
              .getResultList();
  }
  </code></pre>
  
  <p><strong>QUERY</strong></p>
  
  <pre><code>&lt;named-query name='ResourceVO.findByAssignmentIdAndDates'&gt;
      &lt;query&gt;         
          SELECT c FROM Subassignment AS c 
          WHERE (c.assignmentId IN (SELECT b.id FROM Assignment b WHERE b.resource.id = :id))
          AND (c.startDateTime BETWEEN :sdt AND :edt)
  
      &lt;/query&gt;
  &lt;/named-query&gt;
  </code></pre>
  
  <p>I'm always getting an 'Internal Server Error'. Any tips or suggestions?</p>
  
  <p>P.S. The query works fine without the LocalDateTime parameters.</p>
  
  <p>Thanks</p>
  
  <p>Matthijs</p>