org.h2.jdbc.JdbcSQLException: Cannot parse "DATE" constant

10,715 views
Skip to first unread message

gsibley

unread,
Jul 21, 2011, 9:33:40 AM7/21/11
to H2 Database
Hi

I'm extracting data out of an Oracle database, using squirrel, and
inserting it into an 'in-memory' H2 database for my integration tests.
I'm using hibernate annotations and the 'hbm2ddl.auto' utility to
create the schema in the integration tests. I'm using Springs
SimpleJdbcTestUtils.executeSqlScript() method to execute the file of
insert statements. This all works fine except for when I insert data
for DATE fields. The date members in the hibernate classes are
specified as java Date types. e.g.

@Temporal(TemporalType.DATE)
@Column(name = "MY_DATE")
private Date myDate;

Here's a snippet of my insert ddl statement:-

INSERT INTO "MY_TABLE" (ID,MY_DATE) VALUES (1,{ts '2011-07-01
16:19:18.0'});

I get the following stack trace:-

Caused by: org.h2.jdbc.JdbcSQLException: Cannot parse "DATE" constant
"2011-07-01 16:19:18.0"; SQL statement:

Caused by: java.lang.NumberFormatException: For input string: "01
16:19:18.0"

The above works fine if I remove the time portion of the date.

Is there anyway that I can get the time portion recognised?

Regards

George

Shammat

unread,
Jul 23, 2011, 2:58:12 AM7/23/11
to H2 Database
You will need to define your column as TIMESTAMP, a DATE column does
not have a time part.

George Sibley

unread,
Jul 23, 2011, 4:40:30 AM7/23/11
to h2-da...@googlegroups.com
Thanks for the reply. That was pretty obvious. Really sorry for clogging up the forum. My bad.

--
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.


priyanka

unread,
Oct 23, 2012, 1:55:57 AM10/23/12
to h2-da...@googlegroups.com, george....@gmail.com
hello sir,

i want to change date format in h2 database i used this query

SELECT *
FROM pet
WHERE parsedatetime(birth, 'dd-mm-yyyy')  > DATE '21-02-2002';

Iam getting this exception

Cannot parse "DATE" constant "21-02-2002"; SQL statement:
SELECT *
FROM pet
WHERE parsedatetime(birth, 'dd-mm-yyyy')  > DATE '21-02-2002' [22007-169]
22007/22007

Only one question i have
can we change date format in h2 ? how can we change ?


Thanks in advance....

Noel Grandin

unread,
Oct 23, 2012, 9:11:46 AM10/23/12
to h2-da...@googlegroups.com, priyanka, george....@gmail.com

On 2012-10-23 07:55, priyanka wrote:
> SELECT *
> FROM pet
> WHERE parsedatetime(birth, 'dd-mm-yyyy') > DATE '21-02-2002';

Try reading the documentation for the DATE literal
http://h2database.com/html/grammar.html#date
Reply all
Reply to author
Forward
0 new messages