Prepared Statement and Date class - Problem still exists

[Kajol Singh]

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

[JeanLouis Marechaux]

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...

[Kajol Singh]

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

[Stu Glassman]

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

[Kajol Singh]

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

[Stu Glassman]

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

[Stu Glassman]

This method also works for java.sql.Timestamp

[Kajol Singh]

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

[Stu Glassman]

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

[Ruilin Yang]

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

[Kajol Singh]

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

[Ruilin Yang]

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 ]

[Raj Birru]

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);
}
}

[Kajol Singh]

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

[sachin venugopal]

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...

[Stu Glassman]

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:

String[] allempid = new String[5]; //could be int instead of String
allempid[0] = "12";
//etc...
stmt.setArray(2, allempid);

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

[Kajol Singh]

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

[Stu Glassman]

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