ORA-01858: a non-numeric character was found where a numeric was expected

[Ana Ta]

One of my insert statements is throwing this error. There are 2 date fields in the concerned query. I have checked their formats and looks good.

1) Before I check more, I just want to ensure if its because of date field only or any other field can be the cause. (I am actually confused with the second sentence in the 'cause'). Please let me know.

Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.

2) I am using PreparedStatement.setTimeStamp for setting one of the date field and setDate for other Date field. By any chance will setTimeStamp() for a date field cause this error.

PS: I cannot share the query here.

[Jan Cumps]

Ana Ta wrote:... PS: I cannot share the query here.

Can you show us the relevant part? if you change the table name and column names, you will not reveal secret information.

It would help if we could see how you create the prepared statement, how you set the parameters, and what the data types of these fields are in your database.

Regards, Jan

[Ana Ta]

The insert query is a long one with 30+ fields. Snippet of the code below.

pstmt = conn.prepareStatement(insertQuery);
//The following 2 are DATE fields in the DB.

pstmt.setDate(29, new java.sql.Date (DateUtility.getDate(addData.getCreateDate()).getTime()));

if(addData.getLastDate()!=null)
pstmt.setTimestamp(30, new java.sql.Timestamp(DateUtility.getDate(addData.getLastDate()).getTime()));
else
pstmt.setNull(30, java.sql.Types.VARCHAR);

pstmt.addBatch();
int lReturnCode[] = pstmt.executeBatch();

The DateUtility.getDate() function does the following:
public static SimpleDateFormat SHORT_DATE_FORMATTER = new SimpleDateFormat("MM/dd/yyyy");
public static Date getDate(String aDateString) throws Exception {
try{
SHORT_DATE_FORMATTER.setLenient(false);
Date lDate = SHORT_DATE_FORMATTER.parse(aDateString);
} catch (java.text.ParseException pe ) {
// Throws exception
}
return lDate;
}

[Jan Cumps]

You are hiding exceptions in DateUtility.getDate() (you use "// Throws exception" in stead of showing the error)
Maybe something is wrong over there?

Are you shure that you return the correct lDate variable?
You have declared it inside a try block. That means that it is not visible (out of scope) in your return statement.
Is that return statement returning something else than "Date lDate = SHORT_DATE_FORMATTER.parse(aDateString); " ?

Can you do a step by step analysis?
- are my parameters bound to the preparedStatement in the correct sequence (it is easy to make mistakes when you have 30+ fields.)
- check if all is ok in the getDate() method. See what value goes in, and if the Date object that it returns is correct.
- try replacing the two calls to DateUtility.getDate() by a simple 'new Date().getTime()' and see if you get similar exceptions,

etc...