How to send date and time to SQL using java

Question

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?

http://stackoverflow.com/questions/530012/how-to-convert-java-util-date-to-java-sql-date — MikkaRin, Mar 06 '14 at 06:54
covnert it to java.sql.Date format and log to find out if the conversion is correct or not — Rat-a-tat-a-tat Ratatouille, Mar 06 '14 at 06:54

score 6 · Accepted Answer · answered Mar 06 '14 at 06:56

6

Use java.sql.Timestamp class which holds date and time for sql fields, and CallableStatement#setTimestamp:

proc_stmt.setTimestamp(1, new java.sql.Timestamp(fromDate.getTime());

answered Mar 06 '14 at 06:56

Luiggi Mendoza

85,076
16
154
332

score 2 · Answer 2 · answered Mar 06 '14 at 07:00

2

create new object of java.sql date and then pass the java.util date in it.

java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

answered Mar 06 '14 at 07:00

Anita

2,352
2
19
30

score 0 · Answer 3 · answered Mar 06 '14 at 07:01

0

I usually go for along the lines of

proc_stmt.setDate(1, new java.sql.Date(fromDate.getTime()));

answered Mar 06 '14 at 07:01

Scary Wombat

44,617
6
35
64

score 0 · Answer 4 · answered Mar 06 '14 at 07:01

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

How to send date and time to SQL using java

4 Answers4