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
sdesarAsked:
Who is Participating?
 
fargoConnect With a Mentor Commented:
as per your sql statement
SELECT id FROM iss WHERE issue.submit_date > to_char('" + s_issueid + "', 'MM/DD/YY')

The above can only be valid if the data type for the submit_date is VARCHAR.
But in case u have submit_date as data type DATE, u definitely need to have TO_DATE conversion for comparison. The query will become
SELECT id FROM iss WHERE issue.submit_date > TO_DATE('" + s_issueid + "', 'MM/DD/YY')

But one more thing to consider in the last scenario is, that the format of the date should match. Submit date should have MM/DD/YY format then. Better always use the format which is specified in the database.
0
 
evnafetsCommented:
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
0
 
sdesarAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.