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

alexvetter · Accepted Answer · 2012-03-10 21:32:16Z

4

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();

edited Mar 10, 2012 at 21:32

answered Mar 9, 2012 at 0:06

alexvetter

2,0142 gold badges17 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ritesh Ahuja Over a year ago

umm... how will the database know how the fields have been arranged ? what I mean is if my date comes as mm-dd-yyyy the database cannot know that it is arranged that way. Am I correct in thinking so ?

alexvetter Over a year ago

Oh, I think I got you wrong. Yes, if tdate contains a date in another format than "MM/dd/yyy" this won't work. The parser.parse() would throw an exception. So the Statement sqlStmt wouldn't be executed.

Justin Cave · Accepted Answer · 2012-03-08 23:01:28Z

2

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.

answered Mar 8, 2012 at 23:01

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

3 Comments

Ritesh Ahuja Over a year ago

That does insert the correct value into the necessary table. Thanks a lot for that. But when I run query as SELECT * FROM transaction; in sql command line. I only get the date in the time_of_transaction column. I want it to show me the date along with the time.

Justin Cave Over a year ago

@RiteshAhuja - In SQL*Plus, you would need to change your session's NLS_DATE_FORMAT or you'll need to use an explicit TO_CHAR in your query, i.e. select to_char( time_of_transaction, 'YYYY-MM-DD HH24:MI:SS' ) from transaction

Ritesh Ahuja Over a year ago

Thanks a lot for the prompt replies. You guys are awesome! I got my code operational.

Puce · Accepted Answer · 2012-03-08 23:25:40Z

0

Instead of

pStmt.setString(1, "1");

Use:

pStmt.setInt(1, 1);

answered Mar 8, 2012 at 23:25

Puce

38.3k14 gold badges87 silver badges159 bronze badges

1 Comment

Ritesh Ahuja Over a year ago

You are right, I could have used that but it was irrelevant to my question. I just made up something for the sake of it. Nevertheless passing string works just as well.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related