Inserting custom date and current time into Oracle database using string variables

Question

So what I want to do is set a custom date along with the current time into the DATE type in the Oracle database. I get the string from textField in the form of mm/dd/yyyy

 String tdate = textDate.getText().trim();

To simplify the details lets just insert the DATE into a small table.

 Calendar calendar = Calendar.getInstance();                                
 int hour = calendar.get(Calendar.HOUR);
 int minute = calendar.get(Calendar.MINUTE);
 int second = calendar.get(Calendar.SECOND);
 String current_time = hour+":"+minute+":"+second;

now we have tdate as the string of date and current_time as the current time.

to put into a database with table defined as :

create table transaction(
              tranaction_num integer,
              time_of_transaction DATE);

now in jdbc

PreparedStatement pStmt = Conn.prepareStatement("insert into transaction values(?,?));
pStmt.setString(1, "1");
pStmt.setString(2, "to_date( '"+tdate+" "+current_time+"','mm/dd/yyyy hh24:mi:ss')");
pStmt.executeUpdate();

This gives me an error as below

 ORA-01858: a non-numeric character was found where a numeric was expected

I know I am doing something wrong but I have tried so many things to get this working. I don't mind getting the current time some other way as long as it is working

Any help would be appreciated

Solution 1

If you want to pass a string, you probably want something like

String sqlStmt = "insert into transaction values(?,to_date(?,'mm/dd/yyyy hh24:mi:ss'))"
PreparedStatement pStmt = Conn.prepareStatement(sqlStmt);
pStmt.setString(1, "1");
pStmt.setString(2, tdate+" "+current_time);
pStmt.executeUpdate();

From a good coding standpoint, however, you would be much better served doing a setInt for the first parameter and a setDate on the second parameter rather than passing everything as strings.

Solution 2

You should parse the date string before handing it over to the database and you should use setInt() for the first parameter instead of setString()

SimpleDateFormat parser = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");

Date date = parser.parse(tdate+" "+current_time);

String sqlStmt = "INSERT INTO transaction(tranaction_num, time_of_transaction) VALUES(?,?)";
PreparedStatement pStmt = Conn.prepareStatement(sqlStmt);
pStmt.setInt(1, 1);
pStmt.setDate(2, new java.sql.Date(date.getTime()));
pStmt.executeUpdate();