Prepared Statement and Date class - Problem still exists

Hi all,
Thanks for responding to my mail.
My problem is still not solved:
I changed the code to the following, but it still does not work:
String startDate = "to_date('"+newstartDate+"','MM/DD/YYYY HH24:MI:SS')";
String endDate = "to_date('"+newendDate+"','MM/DD/YYYY HH24:MI:SS')";
StringBuffer query = new StringBuffer("select *
from abc
where code = ?
and add_dttm between ? and ?")
PreparedStatement stmt = dbConn.prepareStatement(query.toString());
stmt.setString(1, newvehCode);
stmt.setString(2, startDate);
stmt.setString(3,endDate);
ResultSet rs = stmt.executeQuery();
Could someone help me in solving this problem.
Thanks for your help,
Kaj

Kajol,
Could you post the error you obtain ?
By the way, I'm sure you'll have more answers to your question if you follow the JavaRanch Naming Policy...

The error message I'm getting is:
"A non numeric found where numeric was expected."
Thanks,
Kaj

Kaj, try writing startDate and endDate to System.out and make sure that the values are correct.
What types are startDate and endDate? Why don't you just convert startDate and endDate to java.sql.Date or Timestamp and use stmt.setDate(~~)?
-Stu

Hi all,
Could someone tell me, how to convert java.util.Date to java.sql.Date
Thanks,
Kaj

Try this:
//assuming java.util.Date uDate exists
java.sql.Date sDate = new java.sql.Date(uDate.getTime());
-Stu

This method also works for java.sql.Timestamp

hello all,
Here is some code and its output. I don't understand how month is always getting formatted to '00'.
java.sql.Date startdate = java.sql.Date.valueOf("2002-05-22");
java.text.SimpleDateFormat df1 = new java.text.SimpleDateFormat("yyyy-mm-dd h:mm:ss");
String a = df1.format(startdate);
System.out.println("startDate = " + a);
Output: startDate = 2002-00-22 12:00:00
I appreaciate your help.
Thanks,
Kaj

The SimpleDateFormat is incorrect. "mm" denotes minutes. For months, use "MM"
-Stu

StringBuffer query = new StringBuffer("select *
from abc
where code = ?
and add_dttm between ? and ?")
Please check the data type: add_dttm, startDate and endDate.

Thanks for your reply glassman, your code worked.
Here is the code:
java.sql.Date sdate = java.sql.Date.valueOf(temp);
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd h:mm:ss");
String startDate = formatter.format(sdate);
I have one more question:
How do I convert the formatted String back to java.sql.Date.
I tried:
java.sql.Date sdate1 = java.sql.Date.valueOf(startDate);
but this does not work.
Thanks,
Kaj

You may need to use the following before your above statement.
Connection conn=null;
Statement stmt=null;

try
{
stmt = conn.createStatement();
stmt.setEscapeProcessing (true);
java.sql.Date sdate1 = java.sql.Date.valueOf(startDate);
}
[ July 24, 2002: Message edited by: Ruilin Yang ]

Try this

public class DateFormat {

public static void main ( String[] args )
{
java.sql.Date startdate = java.sql.Date.valueOf("2002-05-22");
System.out.println(" StartDate is "+startdate);
java.text.SimpleDateFormat df1 = new java.text.SimpleDateFormat("yyyy-MM-dd h:mm:ss");
String a = df1.format(startdate);
System.out.println("startDate = " + a);
java.util.Date startDate1 = null;
try {
startDate1 = df1.parse(a) ;
}catch(Exception e){
}
java.sql.Date date1 = new java.sql.Date(startDate1.getTime());
System.out.println("startDate1 = " + startDate1);
System.out.println("date1 = " + date1);
}
}

Thanks everybody for all the suggestions, my issue regarding formatting date and using it in a prepared statement is solved.
Based on the responce I got, I am posting another question I have and hope I will get a good solution for this issue too.
Question: Is it possible to use a 'prepared statement' with an 'in' clause in the query.
example:
String empcode = "xyz";
String allempid = "('12', '13', '14', '15','16')";
String query =("select emp_id from abc where code = ? and emp_id in (?)");

PreparedStatement stmt = dbConn.prepareStatement(query.toString());
stmt.setString(1, empcode);
stmt.setString(2, allempid);
ResultSet rs = stmt.executeQuery();
I appreciate your help,
Thanks,
Kaj

try this
newdate is a string.
//whatever format you have the date in
SimpleDateFormat newformat =
new SimpleDateFormat("dd.MM.yyyy");
java.util.Date sDate = newformat.parse(newdate);
Calendar cal= Calendar.getInstance();
cal.setTime(sDate);
java.sql.Date startD = new java.sql.Date(cal.getTime().getTime() );

Sachin Venugopal
SCJP2
Wannabe SWCD...

I would expect that you can use "in" in a PreparedStatement, however, I'm not sure if you're using it correctly. Try doing it this way:

Try using both your method as well as this one and see if either works.
-Stu

I used Vector instead of String Array, because the size of the array is determined dynamically.
String empcode = "xyz";
Vector v1 = new Vector();
for (-----){
v1.add("sadasd");
}
String query =("select emp_id from abc where code = ? and emp_id in (?)");

PreparedStatement stmt = dbConn.prepareStatement(query.toString());
stmt.setString(1, empcode);
stmt.setObject(2, v1);
ResultSet rs = stmt.executeQuery();
This does not work?
Any suggestions appreciated.
Thanks,
Kaj

First of all, how doesn't this work? Are you getting an exception? A compile error? Nothing at all?
Secondly, try changing the declaration of String query:
String query = "select emp_id from abc where code = ? and emp_id in ?"
If this doesn't work try converting the Vector to an array.
-Stu