This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes EJB and other Java EE Technologies and the fly likes Prepared Statement and Date class - Problem still exists Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Prepared Statement and Date class - Problem still exists" Watch "Prepared Statement and Date class - Problem still exists" New topic
Author

Prepared Statement and Date class - Problem still exists

Kajol Singh
Greenhorn

Joined: Jul 22, 2002
Posts: 15
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


Kaj
JeanLouis Marechaux
Ranch Hand

Joined: Nov 12, 2001
Posts: 906
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...


/ JeanLouis<br /><i>"software development has been, is, and will remain fundamentally hard" (Grady Booch)</i><br /> <br />Take a look at <a href="http://www.epfwiki.net/wikis/openup/" target="_blank" rel="nofollow">Agile OpenUP</a> in the Eclipse community
Kajol Singh
Greenhorn

Joined: Jul 22, 2002
Posts: 15
The error message I'm getting is:
"A non numeric found where numeric was expected."
Thanks,
Kaj
Stu Glassman
Ranch Hand

Joined: Jul 01, 2002
Posts: 91
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
Greenhorn

Joined: Jul 22, 2002
Posts: 15
Hi all,
Could someone tell me, how to convert java.util.Date to java.sql.Date
Thanks,
Kaj
Stu Glassman
Ranch Hand

Joined: Jul 01, 2002
Posts: 91
Try this:
//assuming java.util.Date uDate exists
java.sql.Date sDate = new java.sql.Date(uDate.getTime());
-Stu
Stu Glassman
Ranch Hand

Joined: Jul 01, 2002
Posts: 91
This method also works for java.sql.Timestamp
Kajol Singh
Greenhorn

Joined: Jul 22, 2002
Posts: 15
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
Ranch Hand

Joined: Jul 01, 2002
Posts: 91
The SimpleDateFormat is incorrect. "mm" denotes minutes. For months, use "MM"
-Stu
Ruilin Yang
Ranch Hand

Joined: Jan 06, 2002
Posts: 150
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
Greenhorn

Joined: Jul 22, 2002
Posts: 15
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
Ranch Hand

Joined: Jan 06, 2002
Posts: 150
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
Greenhorn

Joined: Jul 12, 2002
Posts: 24
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
Greenhorn

Joined: Jul 22, 2002
Posts: 15
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
Greenhorn

Joined: Jul 22, 2002
Posts: 4
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
Ranch Hand

Joined: Jul 01, 2002
Posts: 91
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
Kajol Singh
Greenhorn

Joined: Jul 22, 2002
Posts: 15
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
Ranch Hand

Joined: Jul 01, 2002
Posts: 91
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared Statement and Date class - Problem still exists
 
Similar Threads
Oracler Date function
Inserting date in database
Prepared Statement and Date class
Difference between two timestamp fields
Performence issue:Write query directly in session bean or call storted procedure