Until recently LOBase has been displaying dates normally as dd/mm/yyyy, but has suddenly taken to displaying the dates in tables and queries in one of my DBs as as 5-digit numbers, eg. 43801.
If I copy the table into a spreadsheet the date fields show up correctly.
Another LO database on my computer is OK. How do I get this particular DB to behave properly?
Linux MDE3; LO 6.0,6.1,6.3
I’m not actually a user of Base, but would assume that the problem might be related to the fact that Base isn’t the DataBase engine itself, but a kind of interface to one. If you once created or got a database file with embedded RDBMS, it may have used either the older HSQLDB (1.8) or Firebird. The one working as expected may use a different engine anyway.
Note: Firebird embedded RDBMS only reached non-experimental state with LibO V6.2.
ANSWER: haven’t found the reason, but I can work around it as follows:
(1) Copy and paste the defective table to a new one, taking only the definition, but no data
(2) Copy the defective table to a spreadsheet - there, the dates are shown correctly
(3) Append the spreadsheet data to the new DB table. The copying produces an error message about incorrect format, but go ahead just the same.
The appended data in the new table show the dates correctly.
Same problem here with LibreOffice 6.4.6.2 on Ubuntu. Re JVojta’s solution (Base date format not showing correctly), I could not figure out how to change the table view to show the date - doesn’t seem to be an option in my version. And DougRB’s solution to copy and paste into Calc doesn’t work for me either - due to all of the relations between the various tables in my database. (But it is true that when I copied the data into Calc, Calc displayed the date correctly. I just couldn’t copy it back.)
But I did find a workaround. Edit the affected table, click on the date field, and change the Field Properties to provide a sample desired date format.
Isn’t an answer your are writing.
-
Formats will be saved only for tables and in forms. They couldn’t be saved for queries at this moment.
-
Format of a query will read the format of a table.
So: Open the table, right mouse click on the header of the column with the wrong formatted date and there could be chosen “Column Format …”. Set the format for the date at this dialog.
43801 is the unformatted day number that stands for 2019-12-02. The formatting does not matter at all. It is completely irrelevant as long as the value is right. Just apply any format you want in the user interface, that is embedded forms and reports or stand-alone offce documents.