[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Syntax for oracle date in java?

Posted on 2006-05-02
Medium Priority
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 ( 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">
         out.print (s_date);

Please advise

Question by:sdesar

Expert Comment

ID: 16592227
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

fargo earned 100 total points
ID: 16593891
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

ID: 16601406
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!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Moore’s Law has proven itself time and time again since it was first introduced. So what’s next? Will Moore’s law continue to remain relevant, or will new technology take over and bring us the next big advancement in computing?
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

873 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