2

I have a problem with display of dates from Oracle database in a Java webapplication using a JSF datatable. The displayed date differs from the date in the database.

Here are some examples:

in oracle: 24-APR-87 display in datatable: Apr 23, 1987  
in oracle: 01-JAN-10 display in datatable: Dec 31, 2009  
in oracle: 13-MAR-89 display in datatable: Mar 12, 1989

Here is my Java source code: 

public Result getTable() {  
    try {  
        Class.forName(dbDriver);  
        con = DriverManager.getConnection(url);  
        ps = con.createStatement();  
        String query = "SELECT * from " + getTableName();  
        rs = ps.executeQuery(query);  
        return(ResultSupport.toResult(rs));  
    } catch(Exception e) { 
        return(null);
    }  
}

What is the cause and solution for my problem?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
irwan
  • 313
  • 1
  • 4
  • 11
  • 1
    Is the second example supposed to be `01-JAN-10`, so each jsf date appears one day behind? Also, how are you viewing the Oracle dates - SQL*Plus on the same server, SQL Developer, ...? It looks like a timezone issue, maybe, either in the connection to the database or in how you're manipulating the retrieved date once you get it back. – Alex Poole Sep 29 '10 at 06:55
  • yes, you are right it's 01-JAN-2010, i don't familiar with these databse, it's first time for me use it at my program.. can u help me to expalin about timezone issue, or maybe an example about that and about connection to the database and manipulating the retrieved date? – irwan Sep 30 '10 at 02:41
  • http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices – BalusC Sep 30 '10 at 11:29

2 Answers2

1

Oracle stores dates using an internal format that bears no relationship to how it's formatted for display.

What you're seeing is two different date formats being used by different frontends.

To force a query to use the format of your choice, it is best to use TO_CHAR, e.g.:

SELECT TO_CHAR(mydatecolumn,'DD-MON-YYYY') FROM mytable;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • I want my code can be used for all tables . how do I re-construct my code above to continue to be used by all the tables, but also applying TO_CHAR code? – irwan Sep 29 '10 at 04:57
  • The code you have in your question appears to return a result set without converting the dates to strings - perhaps the conversion is being done by the caller? – Jeffrey Kemp Sep 29 '10 at 05:30
  • here is my jsf-config: dataAll polimatPackage.Connection.getData request and here is my jsp code: can you help me to explain or make a solution for these code... – irwan Sep 29 '10 at 05:50
  • so all your tables have a `dateBirth` column? I'm not sure that your generic "getTable" function is the best way here, but I'm not familiar with JSF so will say no more... – Jeffrey Kemp Sep 29 '10 at 07:16
1

Try using f:convertDateTime converter with the JSF output and set the correct timeZone attribute for it.
Example (edit): 

<h:outputText value="#{bean.myDate}">
    <f:convertDateTime timeZone="America/Los_Angeles" type="date" pattern="MM/dd/yyyy" />
</h:outputText>
Adam
  • 5,045
  • 1
  • 25
  • 31