-2

I am trying to retrieve date values from Oracle database using resultset.The format of date is like dd-mmm-yy(like 17-MAY-18).Th column name is VALID_TO here.The default value is set as TO_DATE 01.01.4000 in DB.I am writing code as below.

String sql_qry = "SELECT a.VALID_TO from table1 a where a.VALID_FROM > '01-JAN-
18' and a.VALID_TO > '31-JAN-18'";
this.preparedStatement = dbconnection.prepareStatement(sql_qry);
ResultSet rs = this.preparedStatement.executeQuery();

while (rs.next()) {
            AccountDetails detailsVo = new AccountDetails();
            detailsVo.setDateColumn(rs.getDate("VALID_TO"));
            accountDetails.add(detailsVo);
         }

I am getting the default value 01.01.4000 and not the actual date from database.How to fetch actual values.I imported java.sql.Date above.

user8181539
  • 27
  • 6
  • Where are you formatting the date? – Maurice Perry Feb 20 '18 at 06:49
  • And where is the SQL statement, BTW? – Maurice Perry Feb 20 '18 at 06:50
  • There's a lot missing here: The column definition of `DATE` in the database; the code for `setDateColumn()`; the code you used to print out the date where you got `01/01/4000`. – Jim Garrison Feb 20 '18 at 06:53
  • @MauricePerry Sorry I missed it.I have edited the question.Please can you help me with this – user8181539 Feb 20 '18 at 06:53
  • @user8181539 sorry, I don't get your question. I don't see what the date format has to do with anything; except that you should avoid using date literals in your SQL queries. – Maurice Perry Feb 20 '18 at 06:58
  • The select query only selects "a.Date", and you're trying to get the column "VALID_TO" from the result set. There is no such column in the result set. – JB Nizet Feb 20 '18 at 07:01
  • Actually in Database for column VALID_TO for which I am writing select query data is set in format like 17-FEB-18 also in database itself for same column valid_to data default is DATE_TO 01.01.4000 set in DB.Now when I retrive value from result set I am getting 01.01.4000.May be because I need to convert date.i am not sure why I am getting default value in result set. – user8181539 Feb 20 '18 at 07:03
  • @JBNizet My bad I pasted wrong.Sorry.Its select a.VALID_TO – user8181539 Feb 20 '18 at 07:05
  • 2
    Another question in the category *"why is my database doing this weird thing?"* Usually it turns out the database is not doing a weird thing it's just there's some gap between the actual state of the database and what you think it is. My guess is that the data is different from what you're expecting, perhaps because you haven't committed a change. Or you have populated VALID_TO without a century (i.e. year = `0018`) which is always a danger when using the `YY` format mask instead of `YYYY`. – APC Feb 20 '18 at 07:30
  • An Oracle date literal (assuming bind variables are not an option) is like `date '2018-01-01'`, not `'01-JAN-18'`. – William Robertson Feb 20 '18 at 10:36

1 Answers1

1

tl;dr

  • Use java.time classes rather than legacy date-time classes. Specifically, LocalDate for date-only value.
  • Use placeholders in your SQL and prepared statement.
  • Pass java.time objects directly, via JDBC 4.2.

Example:

myPreparedStatement.setObject( 1 , startLocalDate ) ;
myPreparedStatement.setObject( 2 , stopLocalDate ) ;

…and…

myResultSet.getObject( … , LocalDate.class)

java.time

With JDBC 4.2 and later, you can exchange smart objects with your database rather than dumb strings.

Placeholders

Set up your prepared statement using placeholders rather than literals.

String sql = "SELECT * FROM tbl WHERE fromCol >= ? AND toCol < ? ;" ;

LocalDate

Set up the values to fill-in those placeholders. For a date-only column such as SQL-standard type DATE, use the java.time.LocalDate class. The LocalDate class represents a date-only value without time-of-day and without time zone.

LocalDate start = LocalDate.of( 2018 , Month.JANUARY , 1 ) ;  // First of January 2018.

Half-Open

Generally best in date-time work to use the Half-Open approach to defining a span of time, where the beginning is inclusive while the ending is exclusive. So if you want the entire month of January, query for "is equal to or later than the first of the month AND is less than the first day of the following month". 

LocalDate stop = start.plusMonths( 1 ) ;           // First of February 2018.

Or, your intention might be more clear by using the YearMonth class to represent the entire month as a whole.

YearMonth ym = YearMonth.of( 2018 , 1 ) ;         // January 2018.
LocalDate start = ym.atDay( 1 ) ;                 // First of January 2018.
LocalDate stop = ym.plusMonths( 1 ).atDay( 1 ) ;  // First of February 2018.

PreparedStatement & ResultSet

Either way, we now have a pair of LocalDate objects to feed into our prepared statement's placeholders.

myPreparedStatement.setObject( 1 , start ) ;
myPreparedStatement.setObject( 2 , stop ) ;

When retrieving from the result set:

LocalDate start = myResultSet.getObject( … , LocalDate.class ) ;

Avoid the legacy date-time classes

No need for java.util.Date, java.sql.Date, or any of the other poorly-designed hack date-time classes bundled with the earliest versions of Java.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Using a JDBC driver compliant with JDBC 4.2 or later, you may exchange java.time objects directly with your database. No need for strings nor java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154