]data exception: invalid datetime format while data import into hsql-db

[dil_bert]

hello 

while trying to import data to a hsqldb table i allways get errors.

die data-exception:

Quote

data exception: invalid datetime format do you want to continue

question - should i change

from

 08.12.2020 
09.12.2020 
10.12.2020

to:

20020-12-08 
20020-09-08
20020-10-08

see also: https://stackoverflow.com/questions/39817149/hsqldb-invalid-datetime-format-for-dd-mm-yyyy-but-not-for-yyyy-mm-dd?rq=1

question: Can someone please tell me what is wrong?

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

look forward to hear  from you 

best regarsd

Edited December 30, 2019 by dil_bert

[Barand]

I cannot understand why you would change 

1 hour ago, dil_bert said:

10.12.2020

to

1 hour ago, dil_bert said:

20020-10-08

 

[dil_bert]

dear Barand  i have to do a import of  a calc-based-dataset (365 lines of data) into a libreoffice db 

 

i get the above mentioned error all the time - this nearly drives me nuts  

i have struggled with the dataset for more than a whole week 

 

i try to do some uploads into cloud and give you link to the issued sheets. 

at the moment i think that i have tried out nearly everything that is possible - thinkable. 

 

see the database - a engine that is capable to translate the 7956 verses of the Novum Testamentum  into several (at the moment three) languages

ancient greek 

english 

french 

... all based on the German base - and the yearly new edition of quotes - (that  are published here https://www.losungen.de/download/

 

there fore i have to add the calc seeht /(see the little imabe above ) to the table in the db - called GERMAN_VERSES -  

see the db: NT-Verses-D-EN.odb
see the table  __Losungen__2020__.ods  that has to be imported in the table GERMAN_VERSES

this step allways fails - i do not know why... i allways and ever get the above mentioned error... 

 

dear Barand again many thanks for the quick reply,.... 

btw: (i hoped that i get a reply from you - since you are a true db-expert  

 

 

Edited December 30, 2019 by dil_bert

[Barand]

I appreciate that you need to reformat the date, but why to one with a different day, different month and 18,000 years later?

 

However, one way to do it is to treat your date as a string value initially (ie column type VARCHAR) while you do the import.

Once imported, run an update query to reformat to correct date format

UPDATE mytable SET datum = TO_TIMESTAMP(datum, 'DD.MM.YYYY');

Once you have the correct format you can alter the column from VARCHAR to DATE type

[dil_bert]

Dear Barand 

first of all;  many many thanks for the quick reply - now i managed to go ahead some steps. In other words: i was able to import the data. 

 

But now i want to export the data from Base to a calc sheet.  This is not working as expected. 

 

 

in fact:  i cannot export the data.

about the dataset: there are 365 lines of data - 

i want to export them to a calc sheet - but at the moment i do not manage to do this step. 

 

 

 

[dil_bert]

Dear Barand 

first of all;  many many thanks for the quick reply - now i managed to go ahead some steps. In other words: i was able to import the data. 

The issue: i had a worng data-sheme. in other words: i generated the data set out of a csv-file that was indicating the date-formate as a "text" data-set and not a real and valid date-field. 

so i run into issues and trouble. 

 

finally got there: now it works.  i am very happy. 

 

have a great day