How to send date and time to SQL using java

I am calling a stored procedure in a database. Two of its parameters requires date and time in sql date format.

String x = new SimpleDateFormat("MM-dd-yyyy").format(new Date()) + " 00:00:00 AM";
String y = new SimpleDateFormat("MM-dd-yyyy").format(new Date()) + " 11:59:00 PM";
Date fromDate = null;
Date toDate = null;
try {
   fromDate = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss a").parse(x);
   toDate = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss a").parse(y);
} catch (ParseException ex) {
}
CallableStatement proc_stmt = con.prepareCall("{ call someproc(?,?) }");
proc_stmt.setDate(1, (java.sql.Date) fromDate);
proc_stmt.setDate(2, (java.sql.Date) toDate);

I believe if i send just the date(excluding time), the code works, but its of no use to me as the database does not generate correct results. When i run the above code I get 

ClassCastException:java.util.Date cannot be cast to java.sql.Date

Any solution?

Jon Skeet
people
quotationmark

Why are you formatting a Date to a String and then parsing it again? You shouldn't need a string representation at all. Avoid string conversions as far as you can - you're not really interested in the text representation of the date; you're just trying to specify a value.

You should be able to use:

// TODO: Consider time zones
Calendar calendar = Calendar.getInstance();
// Clear the time part of the calendar (leaving you with "start of day")
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);
Timestamp start = new Timestamp(calendar.getTimeInMillis());

// Adjust to "end of day"
calendar.add(Calendar.DATE, 1);
calendar.add(Calendar.MILLISECOND, -1);
Timestamp end = new Timestamp(calendar.getTimeInMillis());

CallableStatement statement = con.prepareCall("{ call someproc(?,?) }");
statement.setTimestamp(1, start);
statement.setTimestamp(2, end);

Note that I've switched to using java.sql.Timestamp instead of java.sql.Date as it looks like you really do want a date and time whereas java.sql.Date only represents a date.

A couple of other points:

  • If you could use Joda Time, it would make the first part of the code simpler. Joda Time is a much better date/time API in general.
  • I would suggest you change your stored procedure to use an exclusive end time if possible. That way you can just specify the start of today and the start of tomorrow. You don't need to worry about the granularity of the value, because you can always create abutting but non-overlapping intervals. In particular, I've currently only set end to the end of the day down to the last millisecond - but java.sql.Timestamp

people

See more on this question at Stackoverflow