ps.setTimestamp(index, new java.sql.Timestamp(date.getTime()));
But did not work.
Thanks in advance.
The mapping between SQL types and JDBC types in Oracle is a bit complicated, especially for the DATE and TIMESTAMP SQL types.
The Oracle database allows you to store timestamp information in DATE columns, which is quite contrary to the definition of the DATE in the SQL standard. Prior to 9.2 when the TIMESTAMP column support was introduced, Statement.setTimestamp would work for DATE columns. Since 9.2, and until 11.1, the JDBC driver mapped the SQL DATE type to the JDBC Date and the SQL TIMESTAMP type to the JDBC Timestamp. The possible solutions to storing timestamps using the 10.2 JDBC drivers are listed in the Oracle JDBC FAQ, and are reproduced here:
There are several ways to address this problem in the 9.2 through 10.2
drivers:
Alter your tables to use TIMESTAMP instead of DATE. This is probably
rarely possible, but it is the best solution when it is.
Alter your application to use defineColumnType to define the columns
as TIMESTAMP rather than DATE. There are problems with this because
you really don't want to use defineColumnType unless you have to (see
What is defineColumnType and when should I use it? ).
Alter you application to use getTimestamp rather than getObject. This
is a good solution when possible, however many applications contain
generic code that relies on getObject, so it isn't always possible.
Set the V8Compatible connection property. This tells the JDBC drivers
to use the old mapping rather than the new one. You can set this flag
either as a connection property or a system property. You set the
connection property by adding it to the java.util.Properties object
passed to DriverManager.getConnection or to
OracleDataSource.setConnectionProperties. You set the system property
by including a -D option in your java command line.
java -Doracle.jdbc.V8Compatible="true" MyApp
You could also use the JDBC 11.1 drivers (they will work against a 10g instance), and the FAQ has this to state:
Oracle JDBC 11.1 fixes this problem. Beginning with this release the
driver maps SQL DATE columns to java.sql.Timestamp by default. There
is no need to set V8Compatible to get the correct mapping.
V8Compatible is strongly deprecated. You should not use it at all. If
you do set it to true it won't hurt anything, but you should stop
using it.
Finally, found that when storing, we can not do anything. When selecting we have to use TO_CHAR.
Thanks
Just use to_date(?,'YYYY-MM-DD HH24:MI:SS') in your SQL statement and format the date as a string:
SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ps.setString(index, dateTimeFormat.format(date));
It works regardless OJDBC driver version, if you need.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
