Libreoffice base insert query with DATE column

I’m using libreoffice base for a little project. I’m new to it, I usually use myQSL or SQLite for my projects. I can’t insert values in the table where I have a DATE column.

CREATE TABLE SELL 
(
date_ DATE NOT NULL PRIMARY KEY, 
quantity_ NUMERIC(5) NOT NULL,
CONSTRAINT c1 CHECK(YEAR(date_)>2004),
CONSTRAINT c2 CHECK(YEAR(date_)<YEAR(CURRENT_DATE))
);

INSERT INTO SELL VALUES('11/11/02', 3);

And the result is: Wrong data type: java.lang.IllegalArgumentException: 11/11/02

I’m using the Italian settings as Locale that’s why I’m using DD/MM/YY

EDIT: the query is actually
INSERT INTO SELL VALUES(‘11/11/05’, 3);

Please update the relevant bits of the code in the question instead of adding it separately. If you want to add a comment about your edit, say what the piece of old code was and why that was incorrect and had to be replaced by the (then) current value. Thanks!

This code executes correctly (after I have successfully created the SELL table), adding a new record:

INSERT INTO SELL VALUES('2005-11-11', 3);

So it seems SQL ‘expects’ ANSI/ISO date format for an insertion. The date then displays according to your locale setting.

Yeah! You are right, I tried that format but using ‘/’ instread of ‘-’ … Really thanks :wink:

When I try inserting your values via the GUI, I get the following error message :

SQL Status: 23000
Error code: -157

Check constraint violation C1 table: SELL in statement [INSERT INTO “SELL” ( “DATE_”,“QUANTITY_”) VALUES ( ?,?)]

I use a FR locale

which is what is supposed to happen, isn’t it ? After all, the date you are providing is earler than 2004

well sorry I mean INSERT INTO SELL VALUES(‘11/11/05’, 3); obviously 2002 is less than 2004!! I can do it through the guy but not with the query…

I can confirm what you are experiencing via Tools > SQL too. This reminds me vaguely of a bug report about entering dates…

Oh well… Doesn’t matter then… There is so little documentation online that I thought it was my fault!