Date

[Rocko Smith]

I posted in 2 seperate areas of this forum. I wasn't sure where it should go.

I'm trying to assign a date to a column in a oracle table. The format of the date I'm trying to pass is 06/01/2005. I have looked at the api for DATE, but can't figure out how to get the date of 06/01/2005 into the field on the table, which is defined as DATE.
Could somebody help me with this problem?

Here's what I have so far:

sql = "insert into table1(acct_id,date1,date2) values(?,?,?)"

String a = null;
String d1 = "06/01/2005";
String d2 = "06/01/2006";

stmt.setString(1,a);
stmt.setDate(2,d1);
stmt.setDate(3,d2);

[Paul Clapham]

It's a little bit complicated, but here's what you have to do. Your first step is to convert that string to a java.util.Date object:

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date dateD1 = sdf.parse(d1);

Or if that date was supposed to be June 1 and not 6 January, then change the date format in the obvious way. Have a look at the documentation for SimpleDateFormat, it goes into a lot of detail. Then your next step is to create a java.sql.Date object and put it into the PreparedStatement:

java.sql.Date sqlD1 = new java.sql.Date(dateD1.getTime());
stmt.setDate(2,sqlD1);

[stu derby]

Paul has outlined the best way of doing it; here are 2 alternate not-totally-evil ways of doing it.

Approach 1:

// do an explicit string-to-date conversion in the database
sql = "insert into table1(acct_id,date1,date2) values(?,to_date(?,'mm/dd/yyyy'),to_date(?,'mm/dd/yyyy') )";
 
String a = null;
String d1 = "06/01/2005"; 
String d2 = "06/01/2006";
 
stmt.setString(1,a);
stmt.setDate(2,d1);
stmt.setDate(3,d2);

Approach 2:

// do an implicit string-to-date conversion
//   after altering the conversion rules
sql1 = "alter session set nls_date_format = 'mm/dd/yyyy'";
sql2 = "insert into table1(acct_id,date1,date2) values(?,?,?)";
// if pooling or otherwise re-using the connection, the session
//    may need to be altered back
 
String a = null;
String d1 = "06/01/2005"; 
String d2 = "06/01/2006";
 
stmt.setString(1,a);
stmt.setDate(2,d1);
stmt.setDate(3,d2);

The primary reason these are not the best ways is that they push work (parseing the date String into a date object) down to the database that can be done at a higher level; in most n-tiered designs, the deeper resources are more expensive and should not be consumed when it's avoidable.

Approach 2 is also ungood because the alter session statements both require additional trips to the database and they're not ignorable in terms of database performance.

As I said, Paul's approach is the best way, generally. I bother to list these because:
a) there's the rare time such as a one-shot program when you just don't care about the differences and one of these is going to be simpler to get the job done.
b) they illustrate other aspects of Oracle that beginning programmers are often not aware of: built-in functions, implicit conversions (almost always a bad thing), "alter session", etc...

[Rocko Smith]

Paul - Thanks for posting your solution, which I am using in my program.

Stu - Thanks for showing me other options along with an explanation.

I am new to java and am taking advantage of the long holiday week-end to get my struts application working. I am learning so much from reading and posting in this forum.