Libreoffice 6 (64 bit) dates no longer working properly

asked 2019-11-20 12:07:34 +0100

the_watchmann gravatar image

Hi I have upgraded to the latest LO 6.3.3 but a date issue is persistent. I use base to generate queries from a database and then create .csv files with Calc to transfer these files to an accounting package. Unfortunately since upgrading to 64 bit my dates are no longer working properly whereas previously they were consistent. I have checked locale settings for both kubuntu and LO and these are correct, but for some weird reason LO gets the month and date mixed up. (Note this does not happen in Openoffice 4, however OOo Base is too volatile to be of use currently otherwise I would have merrily continued using Oo.) I compared OOo and LO settings and these are the same. (I'm not even going to go into the decimal format that is driving me insane in LO either) All I can think of is that some or other update went awry. I read previous queries, eg "Does your LibreOffice locale need a date acceptance pattern for incomplete date input?" but found no answers to my headache. I have tried changing some inputs but still not working. My Locale settings are for EN_ZA, My OS is Kubuntu 19.04, Laptop is an Intel i7 3520 @ 2.9gHz with 16gb Ram I generally use sqlite date format YYYY-MM-DD, however, LO swaps it around to YYYY-DD-MM. (base is linked to a sqlite3 db). Any ideas?

Regards David

edit retag flag offensive close merge delete

Comments

Sorry, calc changes it to yy/dd/mm...

the_watchmann gravatar imagethe_watchmann ( 2019-11-20 12:09:19 +0100 )edit

Are you getting integer numbers for your dates, when importing your .csv file into calc?. If yes - everything is fine and just a matter of formatting these integers - see also: OASIS ODF Specification about type Date

Opaque gravatar imageOpaque ( 2019-11-20 14:32:10 +0100 )edit

I am exporting the sqlite query to calc, and when calc receives the info, it changes the date. I end up with days being months and months being days and those that calc cannot convert it switches to text format. I found that I can now copy the query to Open Office Calc and then the dates read properly, but as soon as LO Calc comes into play all my dates are wrong. I also pasted the data to a WPS Office installation in an effort to see what happens and WPS also gives the proper dates. It's just LO that cannot get it right.

the_watchmann gravatar imagethe_watchmann ( 2019-11-20 15:57:54 +0100 )edit

There seems to be still a misunderstanding; I'm pretty sure SQLIte query results in text looking like dates (ISO format YYYY-MM-DD) but not dates in the sense of OASIS ODF specification as linked above. Therefore a conversion of text -> integer must occur, when the textual dates are being imported / read by Calc (keyword: date acceptance pattern, which to my knowledge is implicit active for ISO code textual dates even if not defined in Tools -> Options -> Language Settings -> Languages -> Option: Date acceptance pattern). In that sense Calc does notconvert to text but keep text as text. But without any sample output of your "date" SQL query it is hard to tell which problem you really face.

Opaque gravatar imageOpaque ( 2019-11-20 16:42:42 +0100 )edit

There is absolutely no locale that has a date format preset with YDM order. So what have you actually done? It is also not clear to me from your description exactly where or when the date is changed from YYYY-MM-DD to YYYY-DD-MM.

erAck gravatar imageerAck ( 2019-11-21 14:27:25 +0100 )edit

Okay. Let me try and clarify. I have a sqlite db that I manage via LO Base. When I copy a query in base, Calc changes the date, something that neither Open Office nor WPS Office nor Gnumeric does. (Yes, I tested it with all three eventually). Yes, sqlite dates do result in text though I do not know whether base actually influences the result in any way. I copy a query from base and paste said query to a calc spreadsheet, which I then save as a csv file. However, when I paste the query, Calc immediately changes the date. The actual result does not change in other spreadsheets, only in LO.

the_watchmann gravatar imagethe_watchmann ( 2019-11-21 15:31:32 +0100 )edit

and I'm wrong... it's not YY/DD/MM, It actually changes it around to dd/mm/yy but sees the year as the day and vice versa... and no... its been doing this since install. I haven't touched any settings as previously LO worked out of the box.

the_watchmann gravatar imagethe_watchmann ( 2019-11-21 15:58:43 +0100 )edit