How do I query using a date range in H2

[gaboshua]

My current query is as follows:

select count(distinct pdf_name) from qas_issue where
record_disposition ='BLOCKED' and db_inserted between '01-APR-2010'
and '01-MAY-2010'

What is the correct syntax for usin a range?

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[gaboshua]

I modified the date and now it seems the problem is that the date
includes the timestamp. how do I configure it so that it ignores the
timestamp and just gives me the expected results:

error:
Cannot parse timestamp constant 2010-04-01, cause: format hh:mm:ss;
SQL statement:

query:

select count(distinct pdf_name) from qas_issue where

record_disposition ='BLOCKED' and db_inserted between '2010-04-01' and
'2010-05-01'

[Thomas Mueller]

Hi,

The grammar for date / time / timestamp literals is different. See:
http://www.h2database.com/html/grammar.html#date
http://www.h2database.com/html/grammar.html#timestamp

db_inserted between date '2010-04-01' and date '2010-05-10'

> record_disposition ='BLOCKED' and db_inserted between '01-APR-2010'
> and '01-MAY-2010'

Do you use this format in another database? If yes in which one?

> Cannot parse timestamp constant 2010-04-01, cause: format hh:mm:ss;

Could you post the *complete* exception message and stack trace
please? What version of H2 do you use? I can't reproduce this problem.
My test case is:

drop table test;
create table test(ts timestamp primary key);
insert into test values(now());
select * from test where ts between '2000-04-01' and '2110-06-01'

Regards,
Thomas

[gaboshua]

Sorry for the late response. Hear is the entire error message:

select count(distinct pdf_name) from qas_issue where

record_disposition ='BLOCKED' and db_inserted between '2010-04-01' and
'2010-05-01';

Cannot parse timestamp constant 2010-04-01, cause: format hh:mm:ss;

SQL statement:

select count(distinct pdf_name) from qas_issue where

record_disposition ='BLOCKED' and db_inserted between '2010-04-01' and
'2010-05-01' [90011-63]
org.h2.jdbc.JdbcSQLException: Cannot parse timestamp constant
2010-04-01, cause: format hh:mm:ss; SQL statement:

select count(distinct pdf_name) from qas_issue where

record_disposition ='BLOCKED' and db_inserted between '2010-04-01' and
'2010-05-01' [90011-63]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.getSQLException(Message.java:93)
at org.h2.util.DateTimeUtils.parseDateTime(DateTimeUtils.java:
146)
at org.h2.value.ValueTimestamp.parseTimestamp(ValueTimestamp.java:
40)
at org.h2.value.Value.convertTo(Value.java:514)
at org.h2.expression.Function.getValueWithArgs(Function.java:785)
at org.h2.expression.Function.getValue(Function.java:354)
at org.h2.expression.Function.optimize(Function.java:1422)
at org.h2.expression.Comparison.getCast(Comparison.java:89)
at org.h2.expression.Comparison.optimize(Comparison.java:102)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:
119)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:
120)
at org.h2.command.dml.Select.prepare(Select.java:490)
at org.h2.command.Parser.prepareCommand(Parser.java:209)
at org.h2.engine.Session.prepareLocal(Session.java:173)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:
176)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:97)
at java.lang.Thread.run(Unknown Source)

at org.h2.engine.SessionRemote.done(SessionRemote.java:289)
at org.h2.command.CommandRemote.prepare(CommandRemote.java:54)
at org.h2.command.CommandRemote.<init>(CommandRemote.java:37)
at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:
237)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
961)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:143)
at org.h2.server.web.WebThread.getResult(WebThread.java:1392)
at org.h2.server.web.WebThread.query(WebThread.java:1054)
at org.h2.server.web.WebThread.process(WebThread.java:375)
at org.h2.server.web.WebThread.processRequest(WebThread.java:131)
at org.h2.server.web.WebThread.run(WebThread.java:158)


On May 15, 12:18 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Kerry Sainsbury]

What's the datatype for the "db_inserted " column?

Show us the table schema for qas_issue

Cheers

Kerry

[Thomas Mueller]

Hi,

> [90011-63]

You are using H2 version 1.0.63. I suggest to upgrade to a more recent version.