[kensington]

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.setTimestamp(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?

This post has been edited by kensington: 19 November 2007 - 06:16 PM

[Martyr2]

After you insert the date, is it correct even though it is not in the format you want? Because the idea is that you insert the data and you format AFTER you pull it out of the database. Formatting is a presentational layer while databases are a information structure layer. As long as the date is correct in the database (not reading something crazy like December 31 1969 00:00:00 when you wanted todays date) then you should worry about formatting after you pull it out.

Also try using a four digit year with no time part yet. Just try dd/mm/yyyy and see if it goes in fine. Then worry about the time formatting. Make sure that all your parameters are java.sql.Date objects too.

Lastly, when you format it the way you want and try to insert and it fails, what error are you getting for the reason it won't insert?

Thanks

[1lacca]

Instead of

Quote

pstmt.setString(2, sub_date);

use the setDate function of your PreparedStatement, and the driver will take care of the formatting, escaping and everything.