Ask Your Question

Libreoffice base date format

asked 2018-02-02 14:42:52 +0200

ferrat val gravatar image

updated 2018-02-03 20:14:06 +0200

I am working with a table where i have a DATE field. For some time in table view, queries and forms date was in the right format to my locale. But a few days ago everywhere except for forms date became shown in the strange format (days from december 1899 i guess). How can i change that back to normal? When I view original table and queries I get date like "42732", when I view table view and forms I get date like "28.12.16".

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-02-02 17:48:22 +0200

Ratslinger gravatar image


Somewhere along the line the view format of the field(s) was changed. The "strange" format you are seeing is the absolute date - number of days from x where x is the starting date selected in Options (default is Dec 30, 1899).

Check format of the field in table definition. Also, in table view, right mouse click the column & select Column Format... where you select the proper formatting for display.

The formatting is for display purposes only. If a field is defined as a date it will be stored in the database as 'YYYY-MM-DD' regardless.

edit flag offensive delete link more


Thank you for the reply. I have doublechecked the format of the field - it is and was DATE. I tried changing the column format, but it has only temporary effect, when I do the query again the format of the column resets back to that days from 1899 style. Maybe there is a global setting regarding the column format that I have changed somehow?

ferrat val gravatar imageferrat val ( 2018-02-02 22:26:40 +0200 )edit

Then it is most likely because of the query construct. Your question stated everywhere but forms. Sounds like only the query. Probably need to 'cast' the field for proper format. If you have a problem with that, please post the SQL you are using. Modify your question (Edit) if space needed.

Ratslinger gravatar imageRatslinger ( 2018-02-02 22:34:13 +0200 )edit

This may help (quick sample just grabbed):

SELECT "Trash", CAST("Trash" AS "DATE" ) FROM "Table"

Hopefully better example.

Ratslinger gravatar imageRatslinger ( 2018-02-02 22:36:57 +0200 )edit

This doesn't work, unfortunately. I use SELECT "Date", CAST( "Date" AS "DATE" ) FROM "Table" WHERE "Notes" LIKE 'Out%' ORDER BY "Date" DESC ang get two equal columns, where date is in original format like "42732"

ferrat val gravatar imageferrat val ( 2018-02-03 20:08:02 +0200 )edit

If the Field Format of this is set to proper format and you haven't changed language settings in Options I don't understand where your problem is. Can you possibly post a sample .odb without any personal/confidential info?

Ratslinger gravatar imageRatslinger ( 2018-02-03 20:47:09 +0200 )edit

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

Ratslinger gravatar imageRatslinger ( 2018-02-05 14:53:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-02 14:42:52 +0200

Seen: 737 times

Last updated: Feb 03 '18