0

My application is using an Oracle database and one of the table contains a date column. When doing my queries inside SQLDeveloper I need to add the following line to get the date + hours

alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

If I do not use this, I only see the date like 25/11/2016

When I am doing my queries from my JAVA code I don't have the above line thus every date is returned in the format DD/MM/YY

JAVA

String query = "SELECT date from SCHEMA.table";
ResultSet rs = null;
try {
    connection = ConnectionFactory.getConnection(Database.ORACLE);
    preparedStatement = connection.prepareStatement(query);

    rs = preparedStatement.executeQuery();
    while (rs.next()) {
        java.sql.Date dateDB = rs.getDate("date");
    }
catch(SQLException e){
    //error
}

How can I get the date with the hours ? Should I add alter session ... to each query ?

Weedoze
  • 13,683
  • 1
  • 33
  • 63
  • 1
    How do you retrieve the data from the database? And how do you _display_ it from inside your code? **[edit]** your question and add the Java code that retrieves and displays the date. –  Nov 25 '16 at 13:38
  • @a_horse_with_no_name Question updated – Weedoze Nov 25 '16 at 13:41
  • Similar issue is addressed [here](http://stackoverflow.com/questions/21162753/jdbc-resultset-i-need-a-getdatetime-but-there-is-only-getdate-and-gettimestamp) – Abhishek Nov 25 '16 at 13:43

1 Answers1

5

Description part of java.sql.Date:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero

Probably the java.sql.Timestamp is the correct type which includes the time components:

    ...
    java.sql.Timestamp dateDB = rs.getTimestamp("date");
    ...
user85421
  • 28,957
  • 10
  • 64
  • 87
  • 3
    Which means, you need to use `ResultSet.getTimestamp()` not `getDate()` –  Nov 25 '16 at 13:44