title: Hibernate Query search criteria filter by timestamp
body: <p>Good day,</p>
  
  <p>I have a db2 database, and I have a sql query as follow:</p>
  
  <pre><code>select * FROM SCORPORATEREGISTEREDACCOUNTS CRA   WHERE cra.corporateRegisteredAccountId = 241 
  and cra.paymentDate &gt;= '2017-07-07 00:00:00' and cra.paymentDate &lt;= '2017-07-07 23:59:59';
  </code></pre>
  
  <p>And in my database, the row record having paymentDate = '2017-07-07 11:48:00'. This paymentDate column is set as <code>TIMESTAMP</code> for its data type.</p>
  
  <p>When I run this query through command, or through <code>dbVisualizer</code>, I can get the result correctly.</p>
  
  <p>However, When run this query through java code using <code>Hibernate Query</code>, I will have some weird result, which is getting null record for the first time, and have record on the second time and onward, and If I log out from my java web application, and log in again, same problem occur.</p>
  
  <p>Here is my sample code that trigger by a search button:</p>
  
  <pre><code>StringBuilder sb = new StringBuilder( );
  sb.append( " \\ my query here " );
  Query query = getSession( ).createSQLQuery( sb.toString( ) );
  return query.list();
  </code></pre>
  
  <p>In other word, I have to press the search button second time only can get the correct result.</p>
  
  <p>And here is some of my test case:</p>
  
  <ol>
  <li><p>If I change the row record paymentDate to '2017-07-07 00:00:00', then it wont have issue for searching.</p></li>
  <li><p>If I change my sql query to search within 2 day instead of 1 day, then it wont have issue also. </p>
  
  <p>where cra.paymentDate >= '2017-07-07 00:00:00' and cra.paymentDate &lt;= '2017-07-07 23:59:59'</p></li>
  <li><p>Or I change my code to call the query.list() 2 times (only return at second time):</p>
  
  <p>query.list();
  return query.list();</p></li>
  </ol>
  
  <p>However, my solution is cast the column to date:</p>
  
  <pre><code>where date(cra.paymentDate) &gt;= '2017-07-07 00:00:00' and date(cra.paymentDate) &lt;= '2017-07-07 23:59:59'
  </code></pre>
  
  <p>My question is, how come the original query wont work for first time, and only work for second time. If think as logic, it should fulfill the search criteria and return me correct result. Is it a <code>Hibernate Query</code> bugs? Or there is other reason or something wrong with my code.</p>
  
  <p>Kindly advise.</p>