This really is something I have a problem with since yesterday.

I've visits in order to save inside a database. They contain to start dating ? along with a time, like:

01.02.1970 14:00

(german format, in american It could be something similar to 02/01/1970 2:00pm).

First idea: Save it as being a SQL.DATE!

And so i produced a table:

CREATE TABLE appointments (id NUMBER(10) NOT NULL, datum DATE NOT NULL, PRIMARY KEY id)

To date so great.

Now I authored a DAO saving my appointment joined via web form. Later on I needed to create one test, to see if the appointment is saved correctly.

The appropriate test part is the following:

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 ... // <--

<--: Case the part, where I'm not sure things to enter to ascertain if on the specific day to start dating ? already is incorporated in the database.

I attempted:

datum = ?

the the next call of

jdbcTemplate.query(sql, args, rowMapper);

were built with a java.util.Date, a java.util.Calendar or perhaps a java.lang.String ('dd.MM.yyyy') within the args-array, which supports the arguments changing the ? within the prepared statement.

Sure, it was an awful idea, since the database has something similar to

DD.MM.YYYY HH:MI

within the table row (DD=day, MM=month, YY=year, HH=hour, MI=minute).

And So I found the BETWEEN sql command, refactoring (and seeking all type of formats, inputs, strings, resist pass within the args-array) the Choose-command to:

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')

which works, like a number of other tries, basically come in using a sql-tool directly, e.g.

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')

results for instance:

ID                      DATUM                   
----------------------  -------------------
70                      01.02.1970 11:11:11

but my jdbc-get in touch with java always leads to a clear resultset.

Lengthy story, short question:

What's the best practice to question a database, if your date, symbolized with a java-object, is available inside a sql.DATE column inside a database, independet in the with time?

Something similar to this:

PreparedStatement ps = conn.prepareCall("SELECT * FROM table WHERE someDate = ?");
ps.setDate(1, javaDate)

(From memory therefore the syntax might less than be right)

You have to convert java.util.Date objects to java.sql.Date objects though.

This really is quite simple:

java.sql.Date myDate = new java.sql.Date(oldDate.getTime());

Where oldDate is really a java.util.Date object.