Formatting Oracle Date data type insert

[Joseph Smithern]

I have a Date insert that is formatted into an Oracle 9i database and it works.
But I had to make the Date variable a varchar2 in Oracle to get it to work:

java.sql.Timestamp myd = new java.sql.Timestamp(new java.util.Date().getTime());
String sub_date = new SimpleDateFormat("mm/dd/yy , h:mm a").format(myd);
String query = "insert into dept(location, sub_date) values(?, ?)";
PreparedStatement pstmt = conn.prepareStatement(query);                  
pstmt.setString(1, "Jersey");
pstmt.setString(2, sub_date); 
pstmt.executeUpdate();

I also tried pstmt.setDate(2, sub_date) with a Date data type in Oracle and I could get the date to insert but couldnt format it.

Please advise how I can get the Oracle Date datatype to work where I can also format it?

[Jeanne Boyarsky]

Joseph,
Why not store the data as a date in the database and format it on retrieval? This would let you store it (and sort it) as a date. You could use an Oracle function to format it in the query or SimpleDateFormat after pulling it to Java. The Oracle function to_char is described here.

[Joseph Smithern]

Thanks,

It would be like this?

java.sql.Timestamp myd = new java.sql.Timestamp(new java.util.Date().getTime());
String sub_date = new SimpleDateFormat("mm/dd/yy , h:mm a").format(myd);
String query = "insert into dept(location, sub_date) values(?, to_date(sub_date, 'yyyy/mm/dd:hh:mi:ssam')");
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setDate(1, "Jersey");pstmt.setString(2, sub_date);
pstmt.executeUpdate();

[ November 19, 2007: Message edited by: Joseph Smithern ]