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:
(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.
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
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());
oldDate is really a java.util.Date object.