Ask Your Question
0

incorrect date when opening a dbf (foxpro) file

asked 2019-06-11 00:55:50 +0200

repla gravatar image

updated 2019-06-11 18:31:33 +0200

When I open a dbf file (foxpro), date appears to be incorrect.

For example:

01/06/2019 17:05:03 becomes 02/06/2019 10:05:02 and

02/06/2019 21:27:33 becomes 03/06/2019 18:27:32

On both examples the first date/time is correct. I've tried to change date/format settings but I have not been able to correct the date.

Thanks!

SAMPLE FILES:

C:\fakepath\sample_rename_extension_to_dbf.xls (please change the extension to dbf first, this is the only way I could upload this sample file)

C:\fakepath\sample_file_correct_date.xlsx

edit retag flag offensive close merge delete

Comments

Hi - I can confirm that the first screenshot looks the same. I have a MacOS, so the 2nd screenshot - I cannot find it under the preferences.

I did however installed LibreOffice onto Parallels (Win10) and both settings were the same. I opened the file under Win10 LO, and dates were same as what I repored above (i.e. there is no difference between MacOS and Win10).

I have tried to upload a dbf file but the extension is not supported. So I changed it to xls - please change the extension to dbf if you would like to try.

I've also added an xlsx version, which has the correct dates.

Thanks!

repla gravatar imagerepla ( 2019-06-11 18:33:01 +0200 )edit

The numbers are different in xlsx and dbf. Change column format to decimal (not date) and you'll see that the decimal numbers are not the same and the diff is also not a fixed value. Thus, whatever you change the format to, they will never fit (Please note: Dates are internally stored as decimal numbers, where the integer part is the number of the days since start of calculation (see @ebot second screenshot showing Date as the day "0"), while the decimal part represents the fraction of a day - Date formatting is only to representate this decimal number in a human readable format). This comment does not exclude the possibility of LibreOffice importing incorrect numbers (I got no other software enabled to read .dbf files and to compare these with LibreOffice's import result)

Opaque gravatar imageOpaque ( 2019-06-11 18:54:31 +0200 )edit

Yes, I noticed the difference only recently - but cannot understand why there's such difference.

There is a free tool called DBF Viewer Plus, which is great, and this is the tool I used for exporting DBF to xlsx. I know the date displayed by this tool is correct because it is inline with the data where it originates from.

So does it sound like a bug then?

repla gravatar imagerepla ( 2019-06-11 19:21:42 +0200 )edit

So does it sound like a bug then?

Definitely. Please file it.

Mike Kaganski gravatar imageMike Kaganski ( 2019-06-12 03:30:02 +0200 )edit

Filed - thanks.

repla gravatar imagerepla ( 2019-06-12 12:54:54 +0200 )edit

The same happens if the .dbf file is connected against using Base instead of Calc. May it be that the stored date+time values are somehow timezone afflicted?

erAck gravatar imageerAck ( 2019-06-12 13:22:23 +0200 )edit

@erAck: From my understanding DBF file looks like to have a real date format in contrary to calc et.al storing dates as decimal numbers. If (!!!) that's true, importing a dbf file would require some kind of interpretation of that date fomat. And regarding timezone afflicted: Thats why I wrote above in my comment "...the diff is also not a fixed value". Since the diff isn't a fixed value I'd conclude: The issue is not related to some timezone settings.

Opaque gravatar imageOpaque ( 2019-06-12 13:30:23 +0200 )edit

@repla: What is the resulting bug number?

Edit: Nevermind, found it, tdf#125878

erAck gravatar imageerAck ( 2019-06-12 14:54:06 +0200 )edit

And fixed, thanks for the report.

erAck gravatar imageerAck ( 2019-06-13 12:32:16 +0200 )edit

Thank you for your support and for issuing a quick fix.

repla gravatar imagerepla ( 2019-06-13 18:00:41 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-06-13 12:33:30 +0200

erAck gravatar image

Bug tdf#125878, fixed for master, 6.3.0 and probably 6.2.5

edit flag offensive delete link more
0

answered 2019-06-11 13:44:03 +0200

ebot gravatar image

Have you ever checked what's up with you? Menu Tools>LibreOffice>General

image description

Menu Tools>LibreOffice Calc>Calculate

image description

If my answer did solve your problem, please click the big checkmark inside the circle image description to the left of this answer. It then indicates to the other participants that the question has been answered correctly.

edit flag offensive delete link more

Comments

With me Windows 10 Home; Version 1903; 64-Bit | LibreOffice, Version: 6.2.4.2 (x64).

ebot gravatar imageebot ( 2019-06-11 13:44:41 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2019-06-11 00:55:50 +0200

Seen: 96 times

Last updated: Jun 13