Change date format to Oracle Date Format

Question

I am reading few data from a text file using java code,along with date in format (30-OCT-2012 12-22-44-991) and i want to store these details in Oracle Database but in the same format as used by oracle. I tried To_date but of no use, it gives error. Kindly help me.

Please show the code you tried, and the exact error message. — Mat, Jan 20 '13 at 10:44

score 1 · Answer 1 · answered Jan 20 '13 at 10:44

1

Use SimpleDateFormat in Java to parse your String to a java.util.Date. Then use a PreparedStatement and set the date on that.

answered Jan 20 '13 at 10:44

Aleksander Blomskøld

18,374
9
76
82

score 1 · Answer 2 · edited May 23 '17 at 12:03

1

Edit: You can use a PreparedStatement like Aleksander Blomskøld already suggested and with help from Using Oracle to_date function for date string with milliseconds:

final sql = "INSERT into IFT_VEHICLE_STATUS (LATITUDE, LONGITUDE, UPDATED_AT) " +
            "VALUES (?, ?, to_timestamp(?, 'DD.MM.YYYY HH24:MI:SS:SSFF3'))";
final PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 81000000);
pstmt.setInt(2, 162000000);
pstmt.setDate(3, oracleDate);

pstmt.execute();

Old: Are you trying to convert a java.util.Date into a java.sql.Timestamp? You could do that like this:

try {
    final Date javaUtilDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2012-01-20 11:46:06");
    System.out.println("javaUtilDate = " + javaUtilDate);
    final Timestamp oracleDate = new Timestamp(javaUtilDate.getTime());
    System.out.println("oracleDate = " + oracleDate);
} catch (ParseException e) {
    e.printStackTrace();
}

This will give the following output:

javaUtilDate = Fri Jan 20 11:46:06 CET 2012
oracleDate = 2012-01-20 11:46:06.0

edited May 23 '17 at 12:03

Community

1
1

answered Jan 20 '13 at 10:53

Freek de Bruijn

3,552
2
22
28

I converted the date to Timestamp and the oracle column also is Timestamp. but still my query is not working. – prashant nigam Jan 21 '13 at 17:33
query i am using INSERT into IFT_VEHICLE_STATUS (LATITUDE, LONGITUDE, UPDATED_AT) VALUES ('lat','lon','timestampdate'); – prashant nigam Jan 21 '13 at 17:37
Can you use a `PreparedStatement` like Aleksander Blomskøld suggested and with help from http://stackoverflow.com/questions/9180014/using-oracle-to-date-function-for-date-string-with-milliseconds: PreparedStatement pstmt = con.prepareStatement("INSERT into IFT_VEHICLE_STATUS (LATITUDE, LONGITUDE, UPDATED_AT) VALUES (?, ?, to_timestamp(?, 'DD.MM.YYYY HH24:MI:SS:SSFF3'))"); pstmt.setInt(1, 81000000); pstmt.setInt(2, 162000000); pstmt.setDate(3, oracleDate); – Freek de Bruijn Jan 22 '13 at 11:05

Change date format to Oracle Date Format

2 Answers2