CODEWITHSUNDEEP
jdbcoracle10g
SHV
SHV

Reputation: 31

Date-range in JDBC in query to Oracle database, in Java

String sql="select id from period where '"+systemDate+"' between startDate and endDate";

I need to execute this query and I am using Oracle 10g. I want to get the id which falls between these dates. Problem I am facing is in Oracle my date format is "dd-mon-yy" and systemDate is in format "yyyy-mm-dd". So is there any way to convert Java date to dd-mon-format or any other way to execute the above query?

Upvotes: 2

Views: 6868

Answers (3)

Basil Bourque
Basil Bourque

Reputation: 338574

Update: Other Answers are now outdated. The modern solution uses the java.time classes defined in JSR 310. These supplanted the terribly flawed legacy date-time classes such as Date, Calendar, and SimpleDateFormat.

Write your SQL using parameters. Use ? as a placeholder for the value to be passed in at runtime.

Use text blocks for easier reading.

Never use the SQL command BETWEEN in date-time work. Spans of time are best defined using the Half-Open approach, where the beginning is inclusive while the ending is exclusive. In contrast, BETWEEN is fully closed, to include both endpoints. For example, a month starts on the first of the month and runs up to, but does not include, the first of the following month.

String sql = """
        SELECT id_ 
        FROM event_ 
        WHERE NOT ( date_ < ? ) 
        AND date_ < ? 
        ;
        """ ;

You said:

oracle my date format is "dd-mon-yy"

No, incorrect. A DATE column in Oracle database is not text. So it has no “format”.

You said:

systemDate is in format yyyy-mm-dd.

In Java use date-time objects to hold date-time values. Date-time objects are not text, so they have no “format”.

If handed some text as your input date, parse as a java.time.LocalDate object. Your specified format complies with ISO 8601 standard. That standard is used by default in the java.time classes for generating/parsing text. So no need to specify a formatting pattern.

LocalDate start = LocalDate.parse( "2024-01-01" ) ;
LocalDate end = start.plusMonths( 1 ) ; // Cover month of January of 2024.

Pass these objects as the values to be substituted for the ? placeholders in our SQL statement.

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

Upvotes: 0

dogbane
dogbane

Reputation: 274612

You should convert your java.util.Date to a java.sql.Date and use a PreparedStatement as shown below:

java.util.Date jStartDate = ...; //your "java" date object
java.sql.Date startDate = new java.sql.Date(jStartDate.getTime());

java.util.Date jEndDate = ...;
java.sql.Date endDate = new java.sql.Date(jEndDate.getTime());

PreparedStatement p = connection.prepareStatement("select id from period where systemDate between ? and ?");
p.setDate(1, startDate);
p.setDate(2, endDate);
ResultSet rs = p.executeQuery();

Upvotes: 2

jon_darkstar
jon_darkstar

Reputation: 16768

Java has date formatting classes. DateFormat is the abstract parent class for these, SimpleDateFormat here is probably the one you want

SimpleDateFormat oracleStyle = new SimpleDateFormat("dd-MM-yy");
String dString = oracleStyle.format(systemDate);
String sql="select id from period where '"+systemDate+"' between startDate and endDate";

I havent tested this. The format call might need new string buffers as arguments, and my format string could be off, but this is the idea. Read the documentation

Upvotes: 0

Related Questions