conversion from util.Date to sql.Date not working

Question

Why does the below query return this error message: 'data exception: invalid datetime format'? I have researched this and there does not seem to be anything wrong with it?

java.util.Date today = new java.util.Date(); 
releaseDate = new java.sql.Date(today.getTime());
qtd.updateTakenOutTable("insert into takenOutTbl (MovieID, CellPhoneNum, DateTakenOut) VALUES ( ?,      ?, ?)", movieID, num, releaseDate);

and here is the method:

public void updateTakenOutTable(String update, String movieID, String num, java.sql.Date releaseDate) throws SQLException {
    try {
        Connection connection = dc.DatabaseConnection();
        PreparedStatement statement = connection.prepareStatement(update);
        statement.setString(1, movieID);
        statement.setDate(2,releaseDate);
        statement.setString(3, num);
        statement.executeUpdate();
        statement.close();

    } catch (SQLException ex) {
        System.err.println(ex.getMessage());
    }
}

what is qtd.updateTakenOutTable supposed to be doing? Is it expecting a sql.Date parameter? It might be wanting a string in MM/DD/YYYY format. — sproketboy, Aug 06 '14 at 00:35
You are declaring your parameters in the wrong order. They need to be declared in the same order as they appear in the SQL statement. — Gord Thompson, Aug 06 '14 at 00:56
I did do that but now I am receiving this error: integrity constraint violation: unique constraint or index violation; SYS_PK_10179 table: TAKENOUTTBL — Yohi, Aug 06 '14 at 01:11
You are right, with the help of @Robby Cornelissen it turned out to be that I was inserting a new record with the same primary key. — Yohi, Aug 06 '14 at 04:33

score 1 · Accepted Answer · answered Aug 06 '14 at 02:03

1

Seems you are trying to assign releaseDate into CellPhoneNum and num to DateTakenOut.

try

statement.setString(2, num);    
statement.setDate(3,releaseDate);

answered Aug 06 '14 at 02:03

Tapan Pandya

114
2

Prima facie it looks like primary key violation. Check your table description to see what is this constraint about. – Tapan Pandya Aug 06 '14 at 04:32

score 0 · Answer 2 · answered Aug 06 '14 at 01:14

You are mixing how your date and time information is formatted, hence invalid formats... one is "long" and one is not. Can't see the declared types if any.

today is a util.Date see http://docs.oracle.com/javase/7/docs/api/java/util/Date.html

long getTime() Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT >represented by this Date object.

releaseDate is a sql.Date http://docs.oracle.com/javase/7/docs/api/java/sql/class-use/Date.html#java.sql

Reading this documentation carefully, you will see that the type is NEVER represented as "long."

If you want a sql oriented time, perhaps you would rather use ... sql.time? This has its own page on docs.oracle.

The sql.date and sql.time are intended for communicating with SQL databases, while the utility. Date is not. Time is not easily handled across different database systems. The long type you referenced is to count milliseconds like a timer...this timer started in 1970 for the Y2K problem at the turn of the century. It allows comparison on this scale. Note the documents regarding how many milliseconds are in various lengths of time ...

Lot's of good info here. However, the answer to the question is that the parameters were declared in the wrong order. — Dan Bracuk, Aug 06 '14 at 03:03

conversion from util.Date to sql.Date not working

2 Answers2