JDBC - Inserting Date to database produces an error

Question

I get this error when trying to insert a date to my database from microsoft access

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'to_date' in expression.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6956)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3148)
.....

here is the prepared statement that i use

addUserSt = con.prepareStatement("insert into Accounts(Username, First_name, last_name, gender, birthday, email, civil_status, password) "
                 + "values(?,?,?,?,to_date('17-January-97', 'DD-Month-RR'),?,?,?)");

here's how i execute the statement.. dc is an object. The variables like uname, fname, lname etc are all strings.

                dc.addUserSt.setString(1, uname);
                dc.addUserSt.setString(2, fname);
                dc.addUserSt.setString(3, lname);
                dc.addUserSt.setString(4, gender);
                dc.addUserSt.setString(5, email);
                dc.addUserSt.setString(6, civil);
                dc.addUserSt.setString(7, pass);

                dc.addUserSt.executeUpdate();

I don't know why it says that the to_date function is undefined. Could anyone help me with this? Thanks in advance.

Is this query working in an SQL client? No? MSSQL seems [not to have this function](http://stackoverflow.com/questions/2569998/to-date-in-sql-server-2005). — Kai, Nov 21 '12 at 13:39

score 2 · Accepted Answer · edited Nov 21 '12 at 13:43

2

You need to use the CDate() function. This is Microsoft Access version of to_date().

edited Nov 21 '12 at 13:43

Abubakkar

15,488
8
55
83

answered Nov 21 '12 at 13:41

Kevin Bowersox

93,289
19
159
189

score 1 · Answer 2 · answered Nov 21 '12 at 13:44

1

to_date is an Oracle function. Rather use CDate, the Access equivalent.

answered Nov 21 '12 at 13:44

sorencito

2,517
20
21

score 0 · Answer 3 · answered Nov 21 '12 at 16:27

The function to_date is specific to Oracle. As you are not connecting to Oracle but to Access, the function is not available. Instead of using database specific functions, consider using the JDBC escapes syntax for date and time literals (as specified in 13.4.2 of the JDBC 4.1 specification; every JDBC driver should support those). For a date that is:

{d 'yyyy-mm-dd'}

So in your specific example you could use:

values(?,?,?,?,{d '1997-01-17'},?,?,?)

Using escapes is a way to abstract the differences between databases away.

JDBC - Inserting Date to database produces an error

3 Answers3