title: Best Practice: How to check for a specific java.util.Calendar/Date in SQL.DATE by JDBC?
body: <p>This is something I struggle with since yesterday.</p>
  
  <p>I have appointments to save in a database. They consist of a date and a time, like: </p>
  
  <pre><code>01.02.1970 14:00
  </code></pre>
  
  <p>(german format, in american I think it would be something like 02/01/1970 2:00pm).</p>
  
  <p>First idea: Save it as a SQL.DATE!</p>
  
  <p>So i created a table:</p>
  
  <pre><code>CREATE TABLE appointments (id NUMBER(10) NOT NULL, datum DATE NOT NULL, PRIMARY KEY id)
  </code></pre>
  
  <p>So far so good.</p>
  
  <p>Now I wrote a DAO saving my appointment entered via web form. Afterwards I wanted to write a unit test, to check if the appointment is saved properly.</p>
  
  <p>The relevant test part is as follows:</p>
  
  <pre><code>JdbcDao myDao = new JdbcDao();
  myDao.setDataSource(jdbcTemplate.getDataSource());      
  myDao.saveAppointment(appointmentModel);
  
  // Not needed but I saw, the appointment is saved in the database
  setComplete();
  
  // And now for the (sorry for the harsh words) pain in the *** part
  
  String sql = "SELECT id, datum FROM appointments WHERE datum ... // &lt;--
  </code></pre>
  
  <p>&lt;--: This is just the part, where I don't know what to enter to see if on a specific day a date already is in the database.</p>
  
  <p>I tried:</p>
  
  <pre><code>datum = ?
  </code></pre>
  
  <p>the the following call of</p>
  
  <pre><code>jdbcTemplate.query(sql, args, rowMapper);
  </code></pre>
  
  <p>had a java.util.Date, a java.util.Calendar or a java.lang.String ('dd.MM.yyyy') in the args-array, which holds the arguments replacing the ? in the prepared statement.</p>
  
  <p>Sure, this was a bad idea, because the database has something like </p>
  
  <pre><code>DD.MM.YYYY HH:MI 
  </code></pre>
  
  <p>in the table row (DD=day, MM=month, YY=year, HH=hour, MI=minute).</p>
  
  <p>So I found the BETWEEN sql command, refactoring (and trying all kind of formats, inputs, strings, object to pass in the args-array) the SELECT-command to:</p>
  
  <pre><code>String sql = "SELECT id, datum FROM appointments WHERE datum BETWEEN to_date( ?, 'DD.MM.YYYY HH24:MI:SS') AND to_date( ?, 'DD.MM.YYYY HH24:MI:SS')
  </code></pre>
  
  <p>which works, like many other tries, if I enter it via a sql-tool directly, e.g.</p>
  
  <pre><code>SELECT * FROM appointments WHERE datum BETWEEN to_date('01.02.1970 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND to_date('01.02.1970 23:59:59', 'DD.MM.YYYY HH24:MI:SS')
  </code></pre>
  
  <p>outputs for example:</p>
  
  <pre><code>ID                      DATUM                   
  ----------------------  -------------------
  70                      01.02.1970 11:11:11
  </code></pre>
  
  <p>but my jdbc-call in java always results in an empty resultset.</p>
  
  <p>Long story, short question:</p>
  
  <p>What is the best practice to query a database, if a date, represented by a java-object, exists in a sql.DATE column in a database, independet from the given time?</p>