asked on

Syntax for oracle date in java?

I am not sure how to query the database for date .. ie I want to find all the ids where date is > "01/02/06"

With the code below I get no data

This is the Java Code--

public static String getIds( String s_id ) {
String s_result = null;
Connection x_conn = null;
Statement x_stmt = null;
ResultSet x_rset = null;
Logger x_logger = Logger.getLogger ( "Entering" );
x_logger.entering ( "WebUtil", "getIds" );

try {
int i_issueid = Integer.parseInt ( s_id );
x_conn = (Connection) ox_pool.checkout();
x_stmt = x_conn.createStatement();
// x_rset = x_stmt.executeQuery( "SELECT id FROM iss WHERE id >" + i_id ); // works

x_rset = x_stmt.executeQuery( "SELECT id FROM iss WHERE issue.submit_date > to_char('" + s_issueid + "', 'MM/DD/YY')" );//does not work

while ( x_rset.next () ) {
if ( null == s_result ) {
s_result = "<a href=/detail?id=" + x_rset.getString ( 1 ) + ">" +x_rset.getString ( 1 ) + "</a>";
} else { s_result += "<BR>" +"<a href=/detail?id=" + x_rset.getString ( 1 ) + ">" +x_rset.getString ( 1 ) + "</a>" ;
}
}
SqlCleaner.cleanUpSqlSession ( x_rset, x_stmt, x_conn, ox_pool );
} catch ( Exception e ) {
SqlCleaner.cleanUpSqlSession ( x_rset, x_stmt, x_conn, ox_pool );
x_logger.log( Level.FINE, "WebUtil::getIds caught: " + e );
return "";
}
return s_result;
}

This is the JSP : --

String s_date = request.getParameter ( "date" );
out.print ("BLA: " + s_date);
s_date= WebUtil.getIds(s_date);
out.print ("List IDs :" + s_date); //does not print any data

//String s_issues= WebUtil.getIds("433782");

%>

<table border="0">
<tr>

<td>
<%

out.print (s_date);

%>
</td>
</table>

Please advise

Thanks

evnafets

With this " to_char('" + s_issueid + "', 'MM/DD/YY')" don't you mean to_date instead of to_char?
And what is s_issueid? Is that a date or a number?

Personally when dealing with dates, I prefer to use prepared statements:
[code]

String sql = "SELECT id FROM iss WHERE issue.submit_date > ?";
PreparedStatement stmt = x_conn.prepareStatement(sql);

String sDate = ??????;
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date javaDate = sdf.parse(sDate);
java.sql.Date sqlDate = new java.sql.Date(javaDate);
stmt.setDate(1, sqlDate);
x_rset = stmt.executeQuery();
[/code]

Cheers,
evnafets

ASKER CERTIFIED SOLUTION

fargo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdesar

ASKER

Thanks for all the comments.

Fargo's suggestion is right and helps eith my code. I have to make sure that I use to_date and not to_char.

I do not have to do any date conversion. I can just pass a string and use to_date.

Thanks all and here are your excellent points!
Happy Day!