Java Date for Sql insert

Question

I was trying to insert a date into oracle database but I keep facing an conversion issue.

public List<Customer> getCustomerData(Date start, Date end) {
...
String query = "SELECT name FROM customers WHERE trunc(order_timestamp) between trunc(?) and trunc(?)";

DateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
pst.setDate(1, new java.sql.Date(sdf.parse(sdf.format(start)).getTime()));

pst.setDate(2,  new java.sql.Date(sdf.parse(sdf.format(end)).getTime()));

}

From the caller I have

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
Date start = sdf.parse("10/10/2014");
Date end = sdf.parse("10/10/2016");
customerDao.getCustomerData(start,end);

Error I got was:

Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "2014-10-10"; SQL statement:
select name from customers where trunc(order_timestamp) between trunc(?) and trunc(?)  [22018-160]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:158)
    at org.h2.value.Value.convertTo(Value.java:852)
    at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:366)
    at org.h2.expression.JavaFunction.getValue(JavaFunction.java:38)
    at org.h2.expression.Comparison.getValue(Comparison.java:206)
    at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:83)
    at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:90)
    at org.h2.expression.Expression.getBooleanValue(Expression.java:180)
    at org.h2.command.dml.Select.queryFlat(Select.java:514)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:617)
    at org.h2.command.dml.Query.query(Query.java:298)
    at org.h2.command.dml.Query.query(Query.java:268)
    at org.h2.command.dml.Query.query(Query.java:37)
    at org.h2.command.CommandContainer.query(CommandContainer.java:82)
    at org.h2.command.Command.executeQuery(Command.java:185)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
    at com.customerDaoImpl.custDao.getParentContextdata(custDao.java:431)
    ... 29 more
Caused by: java.lang.NumberFormatException: For input string: "2014-10-10"
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1222)
    at java.lang.Double.parseDouble(Double.java:510)
    at org.h2.value.Value.convertTo(Value.java:831)
    ... 44 more

what I think happened was that getTime() will reformat the date into yyyy-MM-dd format and while the input was n MM/dd/yyyy format. But I am not sure if this is exactly what cause the issue.

Edit: H2 doesnt support trunc but you are able to write external script to be invoked.

in Customer.sql

drop ALIAS if exists TRUNC;
CREATE ALIAS TRUNC as '
import java.text.*;
@CODE
double trunc(double num) throws Exception {
    return Math.floor(num*10) /10;
}
';

You need another format (yyyy-MM-dd) or helper method that would try parsing by multiple formats is you're not sure about exact format of input. — Alex Salauyou
– Alex Salauyou, Commented Aug 26, 2015 at 15:38
getTime() returns a number of milliseconds. How could it reformat anything. Post the complete exception stack trace. — JB Nizet
– JB Nizet, Commented Aug 26, 2015 at 15:39
Sounds like you need two DateFormats: One for "yyyy-MM-dd" and another for "MM/dd/yyyy". getTime() returns milliseconds since epoch as a long. No formatting at all. — duffymo
– duffymo, Commented Aug 26, 2015 at 15:39
The error is explicit: you're sending dates as "yyyy-MM-dd" and your pattern is "MM/dd/yyyy". How do you expect this to work? — Luiggi Mendoza
– Luiggi Mendoza, Commented Aug 26, 2015 at 15:40
My question would be why would you do this: pst.setDate(1, new java.sql.Date(sdf.parse(sdf.format(start)).getTime())); when you could do this: pst.setDate(1, new java.sql.Date(start.getTime()));? — Alin Pandichi
– Alin Pandichi, Commented Aug 26, 2015 at 15:44

JB Nizet · Accepted Answer · 2015-08-26 16:09:25Z

3

You're not using Oracle at all, as the stack trace shows. You're using H2. And H2 doesn't seem to have any trunc function accepting a date as argument.

This function call is useless anyway, since you're already passing a date, without any time portion, as argument. The trunc operation, if it existed, would do nothing.

answered Aug 26, 2015 at 16:09

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JB Nizet Over a year ago

That's the problem with this strategy: you end up with tests which pass although they would fail with the real production database, and vice-versa. Don't know why so many people prefer that over losing a few seconds targetting the real database.

logger Over a year ago

updated: as for trunc you are able to write script functions and the database will call that function i have added it to the edit.

Joop Eggen · Accepted Answer · 2015-08-26 16:14:07Z

0

It seems a DATETIME or TIMESTAMP is expected, so use Timestamp instead of sql Date. A java.sql.Date is for days only, setting the parent's class hours, minutes and seconds to zero.

answered Aug 26, 2015 at 16:14

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

1 Comment

logger Over a year ago

Data conversion error converting "2014-10-10 00:00:00.0". It still presists

Alin Pandichi · Accepted Answer · 2015-08-27 07:38:25Z

Your external trunc script expects a double as an input, but you're passing a java.sql.Date. Try this for the script:

drop ALIAS if exists TRUNC;
CREATE ALIAS TRUNC as '
import java.text.*;
import java.lang.*;
import java.sql.Date;
@CODE
double trunc(Date date) throws Exception {
    return Math.floor(new Long(date.getTime()).doubleValue()*10) / 10;
}
';

Although I suspect you would then get another error complaining that you are trying to compare double values with between. Shouldn't your trunc script return a Date too? If it should, then just return the Date:

drop ALIAS if exists TRUNC;
CREATE ALIAS TRUNC as '
import java.text.*;
import java.lang.*;
import java.sql.Date;
@CODE
Date trunc(Date date) throws Exception {
    return date;
}
';

If that works, you can later evolve this script to apply any other operations you want on the date to simulate your trunc behaviour.

For the record, here is the H2 code that is converting your Date into the "yyyy-MM-dd" pattern: ValueDate.java#158. The string value is stored here Value.java#827 and then used by the Double parser method here: Value.java#876

Collectives™ on Stack Overflow

Java Date for Sql insert

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related