CODEWITHSUNDEEP
sqljdbchsqldb
Subodh Joshi
Subodh Joshi

Reputation: 13492

HSQLDB: "invalid datetime format" for dd/mm/yyyy but not for yyyy-mm-dd

My table creation is as follows:

CREATE TABLE inventory (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 100, INCREMENT BY 1) PRIMARY KEY,
  name VARCHAR(30),
  department  VARCHAR(50),
  inventory_type VARCHAR(255),
  expiry_date date NOT NULL
);

Here are the insert statements into the table:

INSERT INTO inventory (name, department,inventory_type,expiry_date) VALUES ('om', 'Education','Raw', '01/01/2016');
INSERT INTO inventory (name, department,inventory_type,expiry_date) VALUES ('hari', 'HR','Solid' ,'02/02/2016');
INSERT INTO inventory (name, department,inventory_type,expiry_date) VALUES ('hariom', 'Finance','Other', '03/03/2016');

Unfortunately, I get

Caused by: java.sql.SQLDataException: data exception: invalid datetime format at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:457) ~[spring-jdbc-4.1.6.RELEASE.jar:4.1.6.RELEASE] ... 60 common frames omitted

Can someone please tell me what is wrong?

When I change statements like below, it's working fine:

INSERT INTO inventory (name, department, inventory_type, expiry_date) VALUES ('om', 'Education','Raw', '2016-01-01');
INSERT INTO inventory (name, department, inventory_type, expiry_date) VALUES ('hari', 'HR','Solid' ,'2016-02-02');
INSERT INTO inventory (name, department,inventory_type,expiry_date) VALUES ('hariom', 'Finance','Other', '2016-03-03');

Upvotes: 5

Views: 7969

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123584

Can someone please tell me what is wrong?

The answer is simply that HSQLDB will only accept string literals for dates if they follow the 'yyyy-mm-dd' format. It is willing to be helpful – to a point – in that it will allow us to omit the DATE keyword from a "proper" HSQLDB date literal value

DATE '2016-01-02'

and let us simply provide

'2016-01-02'

but it will not accept '01/02/2016' (which is ambiguous, anyway) or any of the many other ways that we silly humans can represent dates.

Upvotes: 5

Related Questions