This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Insert date and time into Oracle database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert date and time into Oracle database" Watch "Insert date and time into Oracle database" New topic
Author

Insert date and time into Oracle database

smitha rai
Greenhorn

Joined: Jun 29, 2001
Posts: 18
Hi,
I am Htrying to insert current date and time into the oracle database. I am getting error like "ORA-01821: date format not recognized". My Code is below. Please help me out.
==========================================
public void InsertData() {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
JDBCOracleConnection1 joc = new JDBCOracleConnection1();
try {
con = joc.getOracleConnection();
stmt = con.createStatement();
con.setAutoCommit(false);
java.util.Date myDate = new java.util.Date();
String strDate = "";
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddhhmmss");
strDate = formatter.format(myDate);
String query = "INSERT INTO TESTING VALUES('1500', TO_DATE('" + strDate + "', 'dd/MMM/yyyy, hh:mm:ss a'), 'AAA')";
stmt.execute(query);
con.commit();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
} catch(MyException e){
System.out.println("MException Printed is : " + e);
} catch(Exception e){
System.out.println("Exception:" + e);
}
finally {
joc.closeAll(stmt, con);
}
}
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
first off, you can use sysdate and not have to format a date at all. Sysdate being oracle's current date/time
insert...... values(1500, sysdate, 'AAA')
Otherwise
insert..... values(1500, to_date('20010620 175059', 'YYYYMMDD HH24MISS'), 'AAA')
... I like to use the HH24 military time
Dan
[This message has been edited by Daniel Dunleavy (edited June 29, 2001).]
[This message has been edited by Daniel Dunleavy (edited June 29, 2001).]
smitha rai
Greenhorn

Joined: Jun 29, 2001
Posts: 18
Hi Dan,
Thanks for the reply.
insert...... values(1500, sysdate, 'AAA') is working perfect. But I could not able to retrieve the time. When I execute the program, I am getting the result as : "The Converted date is : 06/29/2001 00:06:00" which is in correct. I am using the following method to retieve the converted time.
public static String getFormattedDate(java.util.Date dat, String format) {

String dateStr = null;
SimpleDateFormat formatter;
if (format == null)
formatter = new SimpleDateFormat(defaultFormat);
else
formatter = new SimpleDateFormat(format);
dateStr = formatter.format(dat);
return dateStr;
}
For reading the data, I am using the following method.
public void ReadData() {

Connection con = null;
Statement stmt = null;
Statement stmt1 = null;
ResultSet rs = null;

JDBCOracleConnection1 joc = new JDBCOracleConnection1();

try {

con = joc.getOracleConnection();
stmt = con.createStatement();
stmt1 = con.createStatement();

java.util.Date dt = new java.util.Date();

String query = "select * from testing";


con.commit();
rs = stmt.executeQuery (query);

while (rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getDate(2) + " " + rs.getString(3));
System.out.println("The Converted date is : " + getFormattedDate(rs.getDate(2),dateTimeFormat));
}

} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
} catch(MyException e){
System.out.println("MException Printed is : " + e);
} catch(Exception e){
System.out.println("Exception:" + e);
}
finally {

joc.closeAll(stmt,rs,con);
}
}
public static void main (String args[]){



DateTest dT = new DateTest();

dT.upDateData();
dT.ReadData();

}
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
You could bring back the date as a string using the oracle to_char command and avoid all the java code
Otherwise, I am new to java and haven't used the formatter, so I would suggest you play with the format string.
Dan
[This message has been edited by Daniel Dunleavy (edited June 29, 2001).]
Mohamed Yousuff
Ranch Hand

Joined: Jun 23, 2001
Posts: 73
Hai,
I think I have some solution for you. I assume the problem as follows. You are able to insert date into the table 'test' using sysdate and just want to retrieve the time out of it. Try the query
"select to_char(sysdate, 'hh:mm:ss') from test"
I have personally tested this query and found to be working
------------------
 
 
subject: Insert date and time into Oracle database
 
Similar Threads
UTC formatting
need java logic
problem while insert date data in mysql
Date, time and timestamp
inserting a date field