Syntax for oracle date in java?

Posted on 2006-05-02
Last Modified: 2012-05-05
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 ( () ) {
            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">
         out.print (s_date);

Please advise

Question by:sdesar
    LVL 4

    Expert Comment

    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:

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

    LVL 11

    Accepted Solution

    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.

    Author Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now