problem with java.util.Date()

[Nikhil Chavan]

Hello everyone, here is one problem I am facing while using java.util.Date() class.

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
sdf.format(new java.util.Date());
SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
sd.format(new java.util.Date());

Statement stmt = Conn.createStatement(); // Conn object is already created
String query = "select sum(ctotal) from creg where date_time between '"+sd.format(new java.util.Date())+"' and '"+sdf.format(new java.util.Date())+"'";
ResultSet rs = stmt.executeQuery(query);
if(rs.next())
{
totalCash = rs.getFloat(1);
}

Here in this code, I'm not getting the proper value for "new java.util.Date()" when using JSP with Tomcat5.5 (jakarta-tomcat-5.5.9)??
I cant trace what's the exact problem.
All suggestion are welcome !
Thanks.

[Jeanne Boyarsky]

Nikhil,

You should use a PreparedStatement rather than a Statement to insulate your code from the format the database wants a Date in. Then your query looks like:
select sum(ctotal) from creg where date_time between ? and ?

and you can call:
java.util.Date now = new java.util.Date();
preparedStatement.setDate(1, new java.sql.Date(now.getTime()));

[Jeanne Boyarsky]

I'm going to move this to our JDBC form since it is about data access.

And welcome to JavaRanch!

[Rene Larsen]

Originally posted by Jeanne Boyarsky:
Nikhil,

You should use a PreparedStatement rather than a Statement to insulate your code from the format the database wants a Date in. Then your query looks like:
select sum(ctotal) from creg where date_time between ? and ?

and you can call:
java.util.Date now = new java.util.Date();
preparedStatement.setDate(1, new java.sql.Date(now.getTime()));

And if you also want to compare on the date AND time, then you need to use java.sql.Timestamp instead of java.sql.Date in the preparedStatement.

java.util.Date now = new java.util.Date();
preparedStatement.setTimestamp(1, new java.sql.Timestamp(now.getTime()));
[ August 30, 2008: Message edited by: Rene Larsen ]