how to convert java.util.Date to the format accepted by oracle?

Question

i want to pass date as a parameter in my stored procedure which accepts date parameter. the date format my oracle db accepts e.g 10-JUL-17

now i tried

SimpleDateFormat df = new SimpleDateFormat("dd-MMM-yy");
Date d = new Date();
String newdate = df.format(d);
System.out.println(newdate);

it prints 13-JUL-17 but as it is string my table won't accept this value. how can i convert it to the date datatype so that oracle will accept it.

`Date`s do not have a format. If you need a `Date` object for your Oracle db, format is irrelevant. Maybe you have a problem with your Oracle code that you haven't posted. — khelwood, Jul 13 '17 at 11:57
maybe this can help? https://stackoverflow.com/a/31238011/4273199 — Mischa, Jul 13 '17 at 11:58
Beside all the conversions others are suggesting you, I'd go marking the date as a query parameter (i.e. with `?`) and use the JDBC driver methods to provide the relevant Java object (i.e. Date, Timestamp, etc) — watery, Jul 13 '17 at 12:04
A [`DATE` does not have a format](https://stackoverflow.com/documentation/oracle/2087/dates/6848/the-format-of-a-date); it is stored internally as 7- or 8-bytes representing year (2-bytes), month, day, hour, minute and second (1-byte each). The user interface you use (SQL/Plus, SQL Developer, Toad, Java, etc.) will typically apply a format to the date so that it is human readable but this is not done by the database. The format used by SQL/Plus and SQL Developer is the `NLS_DATE_FORMAT` session parameter which is stored in the database but any user can independently change their own settings. — MT0, Jul 13 '17 at 13:21

Usagi Miyamoto · Answer 1 · 2017-07-13T13:25:52.647

2

If you want to set an argument to the given date value, you should use the setDate() method of your PreparedStatement instance.

However this method needs a java.sql.Date instance, and if your date is a java.util.Date, then you have to convert it: new java.sql.Date(java_util_date.getTime());

EG:

try ( PreparedStatement ps = con.prepareStatement( "SELECT * FROM table WHERE dat >= ?" ))
{
  ps.setDate(1, new java.sql.Date( System.currentMillis() ) );
  try ( ResultSet rs = ps.executeQuery() )
  {
    while ( rs.next() )
    {
      // process the row in rs
    }
  }
}

Edit

For stored procedure use the following syntax in the SQL query:

{call stored_procedure(?)}

Also, use executeUpdate() instead of executeQuery(), if your stored procedure does not return a ResultSet...
(If i remember correctly, there were some issues with the call keyword, especially with the case. So try CALL if call does not work...)

edited Jul 13 '17 at 13:25

answered Jul 13 '17 at 12:04

Usagi Miyamoto

6,196
1
19
33

to me it doesn't matter if it's java.util.Date or java.sql.Date i just need to insert the current date in a 13-JUL-17 format – sagar limbu Jul 13 '17 at 12:06
Java dates do not have a format, but they can be set to JDBC parameters. – Usagi Miyamoto Jul 13 '17 at 12:14
how do i do the same thing with stored procedure? – sagar limbu Jul 13 '17 at 12:24
@sagarlimbu Just use a CallableStatement instead of a PreparedStatement and set the bind parameters on that instead. – MT0 Jul 13 '17 at 13:21

score 1 · Answer 2 · answered Jul 13 '17 at 12:15

There is no format for Date fields in Oracle. As long as the datatype of the DB field is Date and you are passing the value as Date it should work. If you are passing the date as a String then your sql will have to use the TO_DATE function to convert the value. Try converting your date value into java.sql.Date before passing it to the stored procedure.

score 0 · Answer 3 · answered Jul 13 '17 at 12:02

0

You can use TO_TIMESTAMP that converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

answered Jul 13 '17 at 12:02

DevRj

448
8
19

score 0 · Answer 4 · answered Jul 13 '17 at 12:07

0

If You're using entity to bind your data, simple

@Column(name = "DATE")
@Temporal(TemporalType.TIMESTAMP)
private Date date;

will do

answered Jul 13 '17 at 12:07

PanBrambor

89
9

score 0 · Answer 5 · answered Jul 13 '17 at 12:18

Use the TO_DATE function. For example:

INSERT INTO SOME_TABLE
  (NUMBER_FIELD, TEXT_FIELD, DATE_FIELD)
VALUES
  (1,            'ABC',      TO_DATE(:p1, 'DD-MON-RR'))

You can find the docs for the TO_DATE function here and the documentation for date format strings here.

Best of luck.

score 0 · Answer 6 · answered Jul 13 '17 at 12:55

Totally agree with the other comments and answers suggesting:

Formatting as a Date using PreparedStatement and setDate()
Using other standard JDBC driver functions
Being aware that all of this is locale dependent

However, some old Cobol programmers (who don't know anything about Java or Date) taught me that you can use a String of the format DDMMMYYYY.

The following would be acceptable to Oracle 11g, with the en-US locale:

UPDATE table_name
   SET date_field = '10JUL2017'
 WHERE id = 1;

how to convert java.util.Date to the format accepted by oracle?

6 Answers6

Edit