Ask Your Question
1

libreoffice base insert query with DATE column [closed]

asked 2014-01-23 15:15:25 +0200

rfringuello89 gravatar image

updated 2014-01-23 15:38:56 +0200

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);

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 03:47:29.263572

Comments

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!

bencomp gravatar imagebencomp ( 2014-01-24 00:13:02 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2014-01-24 03:36:56 +0200

frofa gravatar image

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.

edit flag offensive delete link more

Comments

Yeah! You are right, I tried that format but using '/' instread of '-' ... Really thanks ;)

rfringuello89 gravatar imagerfringuello89 ( 2014-01-24 09:46:18 +0200 )edit
0

answered 2014-01-23 15:35:55 +0200

Alex Thurgood gravatar image

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

edit flag offensive delete link more

Comments

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

Alex Thurgood gravatar imageAlex Thurgood ( 2014-01-23 15:37:31 +0200 )edit

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

rfringuello89 gravatar imagerfringuello89 ( 2014-01-23 15:38:07 +0200 )edit

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

Alex Thurgood gravatar imageAlex Thurgood ( 2014-01-23 17:43:22 +0200 )edit

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

rfringuello89 gravatar imagerfringuello89 ( 2014-01-23 19:03:26 +0200 )edit

Question Tools

3 followers

Stats

Asked: 2014-01-23 15:15:25 +0200

Seen: 2,944 times

Last updated: Jan 24 '14