Hi,
I came across this issue. Essentially the problem seems to be that:
CURRENT_DATE+1 // works (adds one day)
?+1 // where ? is set as a date
does not work
Which seems to me to be inconsistent. I realise that I should be using
the dateadd/datediff methods really in anycase, it just seems odd that
overloading '+' would be just partially supported for dates. See the
test case below. Same problem using both 1.2.147 and 1.3.154.
- mike
---------------- test case -----------------------
package dev.scratch;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PokeDateArithmetic {
/////////
// JDBC nonsense
////
static{
try {
Class.forName("org.h2.Driver");
} catch (Exception e) {
throw new java.lang.Error("ERROR: failed to load JDBC
H2Database
driver.", e);
}
}
static public void main(String[] args) throws Exception {
Connection h2Conn =
DriverManager.getConnection("jdbc:h2:mem:aname",
"sa", "");
runTest(h2Conn);
}
static public void runTest(Connection conn) throws Exception{
PokeDateArithmetic pb = new PokeDateArithmetic(conn);
pb.addDate("2010-10-01");
pb.addDate("2020-10-01");
{
PreparedStatement ps = conn.prepareStatement("SELECT *
FROM DATES
WHERE CURRENT_DATE<val");
ResultSet rs = ps.executeQuery();
while(rs.next()){
String s = rs.getString(1);
System.err.println(s);
}
}
{
PreparedStatement ps = conn.prepareStatement("SELECT *
FROM DATES
WHERE ?<val");
ps.setDate(1, new
java.sql.Date(System.currentTimeMillis()));
ResultSet rs = ps.executeQuery();
while(rs.next()){
String s = rs.getString(1);
System.err.println(s);
}
}
{
PreparedStatement ps = conn.prepareStatement("SELECT *
FROM DATES
WHERE CURRENT_DATE+1<val");
ResultSet rs = ps.executeQuery();
while(rs.next()){
String s = rs.getString(1);
System.err.println(s);
}
}
{
PreparedStatement ps = conn.prepareStatement("SELECT *
FROM DATES
WHERE ?+1<val");
ps.setDate(1, new
java.sql.Date(System.currentTimeMillis()));
ResultSet rs = ps.executeQuery();
while(rs.next()){
String s = rs.getString(1);
System.err.println(s);
}
}
}
final Connection conn;
public PokeDateArithmetic(Connection conn) throws SQLException{
this.conn = conn;
conn.createStatement().execute(
"CREATE TABLE dates ("+
"val DATE)"
);
}
public void addDate(String d) throws SQLException{
PreparedStatement ps = conn.prepareStatement(
" INSERT INTO dates(val)" +
" VALUES(?)");
ps.setDate(1,java.sql.Date.valueOf(d));
ps.execute();
}
}
------------- outputs -------------------
2020-10-01
2020-10-01
2020-10-01
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Data
conversion error converting "2011-04-26"; SQL statement:
SELECT * FROM DATES WHERE ?+1<val [22018-154]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.value.Value.convertTo(Value.java:847)
at org.h2.expression.Operation.getValue(Operation.java:108)
at org.h2.expression.Comparison.getValue(Comparison.java:201)
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:290)
at org.h2.command.dml.Query.query(Query.java:260)
at org.h2.command.dml.Query.query(Query.java:37)
at org.h2.command.CommandContainer.query(CommandContainer.java:78)
at org.h2.command.Command.executeQuery(Command.java:181)
at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
at dev.scratch.PokeDateArithmetic.runTest(PokeDateArithmetic.java:62)
at dev.scratch.PokeDateArithmetic.main(PokeDateArithmetic.java:24)
Caused by: java.lang.NumberFormatException: For input string: "2011-04-26"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:458)
at java.lang.Integer.parseInt(Integer.java:499)
at org.h2.value.Value.convertTo(Value.java:804)
... 13 more
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
