date and query

[mark smith]

hi

i have problem to use a date in a query

Date datePublication;
cal.add(Calendar.MONTH,-12);
datePublication = genererDate(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), 1);
 
public Date genererDate(int pYear, int pMonth, int pDay){
  String strDate = Integer.toString(pYear) + "-" +  Integer.toString(pMonth) + "-" + Integer.toString(pDay);
  java.util.Date parsedDate = new java.util.Date();
                
  try {
        parsedDate = new SimpleDateFormat("yyyy-MM-dd").parse(strDate);
  } catch (ParseException e) {
        e.printStackTrace();
  }     
  return new java.sql.Date( parsedDate.getTime() );
}
 
datePublication show me
2005-10-01
 
i use directely in a query datePublication...
 
i displayed the query
[code]
select * from test where datePub >= 2005-10-01 

i get the error:

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

any idea?

[Kaydell Leavitt]

Hi.

I think that that the problem is in your select statement. If the datePub field of the database table is a string then your date should be withing single quotes.

-- Kaydell

[Jesper de Jong]

This is not a Java problem - it's an SQL problem. Your SQL statement:

select * from test where datePub >= 2005-10-01

is not a valid SQL statement. The best way to do queries or updates with parameters in Java is by using PreparedStatement, like this:

// The database connection
Connection db = DriverManager.getConnection(...);
 
// The PreparedStatement, notice the '?' at the place where parameters come
PreparedStatement ps = db.prepareStatement("select * from test where datePub >= ?");
 
// Set the first parameter to your Date object - NOTE: Must be a java.sql.Date (not java.util.Date)
ps.setDate(1, new java.sql.Date(parsedDate.getTime()));
 
// Execute the query and get the result set
ResultSet rs = ps.executeQuery();
 
// Use the result set
// ...
 
// Don't forget to close result set and statement
rs.close();
ps.close();