Bug Hunting Session
Bug 107012 - date with custom format macro and locale other than English don't produce desired result
Summary: date with custom format macro and locale other than English don't produce des...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium minor
Assignee: Eike Rathke
URL:
Whiteboard: target:5.4.0 target:6.2.0 target:6.1....
Keywords:
Depends on:
Blocks:
 
Reported: 2017-04-07 10:09 UTC by Adrian Georgescu
Modified: 2018-08-22 07:34 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Adrian Georgescu 2017-04-07 10:09:05 UTC
When using the basic function Format in a macro (for Writer) and other locale language settings than English it gives me wrong results.

Macro:
sub addDateText
    dim document   as object
    dim dispatcher as object

    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

    dim sText as string
    sText = Format(Now(),"dd/mm/yyyy")

    dim textFormatat(0) as new com.sun.star.beans.PropertyValue
    textFormatat(0).Name = "Text"
    textFormatat(0).Value = sText+CHR$(9)+"[SomeText]"    
    dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, textFormatat())
end sub


With locale set to ro-RO: 07.04.2017 [Some text]
With locale set to en-US: 07/04/2017 [Some text]
With locale set to en-UK: 07/04/2017 [Some text]
With locale set to fr-FR: 07.04.2017 [Some text]
With locale set to de-DE: 07.04.2017 [Some text]

after modifying sText = Format(Now(),"dd\/mm\/yyyy") it produced the desired results.

I've tried but didn't work changing the Date accepted patterns in Options->Language Settings->Languages.
Comment 1 Gerhard Weydt 2017-04-11 13:46:15 UTC
I can confirm this on Windows 10 64 bit, LibO 5.1.3.3, with the exception of fr-FR where the slashes are used in my instance (in accordance with my hypothesis stated below: slash is the fisrt separator; in contrast to fr-CA where the hyphen comes first).

A shorter way to show the effect is to execute simply

    msgbox Format(Now(),"mm/dd/yyyy")

But I'm not sure whether this is a bug or it is intional. My hypothesis, based on incomplete tests, is that LibO replaces the slash by another separator if a different one is to be found in the first date acceptance pattern for the locale used (it seems to be depending only of the first separator in the format parameter, by the way). Perhaps the slash is regarded as the standard separator, and is only replaced if there is another principal separator for the locale setting.
As the documentation is not detailed enough, one can only be sure by looking at the code, which I cannot.

As far as I know, date acceptance patterns are entered into the code of Lobo, so it's no wonder you could not change them.

As the problem can be easily overcome by using the backslash, as you found out by yourself, I would propose that you set the importance to "minor".

Gerhard
Comment 2 Eike Rathke 2017-04-13 12:26:34 UTC
That is expected. Separators are exchanged with those of the output locale, so for example for the format "0.00" a value 123.45 is displayed as "123,45" if the locale uses a comma as decimal separator. The canonical way to force a string instead of a separator *is* to quote it, e.g. by preceding it with a \ backslash.

However, for date formats the result can be misleading if the date order is not identical between locales, e.g. MDY in en-US vs DMY in de-DE. In the example the date for de-DE should be displayed as 04.07.2017 instead. This is the actual bug encountered.

Date acceptance patterns are completely unrelated to this, they determine what input is accepted as date when parsing a string.
Comment 3 Gerhard Weydt 2017-04-13 15:49:20 UTC
If I understand Eike's answer correctly, then the slash in the second parameter of FORMAT doesn't mean the character "slash", but represents the separator between day, month and year, which is changed to the local separator according to the locale used, and this will only work correctly, if you use the order of day, month and year which corresponds to the locale.
Indeed, Format(Now(), "DD/MM/YYYY") yields 13.04.2017 with german locale, whereas Format(Now(), "MM/DD/YYYY") yields 04/13/2017, with correct numbers, but another separator.
In my tests I found out that you often can use period or hyphen as separator, as well, but this does not work in all cases.
So the recomendation wold be to use the correct separator *and* the correct sequence fitting your locale for the format in accordance with the local, or quote the character you wish to force withe backslash.
If the separator used is not connected with the date acceptance pattern, it must be defined within the locale. And it will normally coincide with the first date acceptance pattern for a locale, so it's no wonder I didn't find a case contradicting my hypothesis.
I will propose an addition to the help text in a new bugzilla entry.

@Adrian: I think your bug can be regarded as resolved, but I leave it to you to change the status accordingly.
Comment 4 Adrian Georgescu 2017-04-14 07:01:14 UTC
Ok, now I understand this is how LO works and if I want to insert a custom separator I need to put backslash char in front of it. I was accustomed to MS (till recently when my company adopted the LO) and it didn't need a "special" char to display a custom separator so from there it generated my 
misunderstanding. I have to unlearn some things and learn another. Thanks for your answers.
Comment 5 Eike Rathke 2017-04-18 11:52:24 UTC
This is not resolved though, I'm keeping the bug for the wrong date order if the actual locale one's is different from MDY (which it usually is). Bear in mind that the BASIC locale is en-US and formats are converted to the output locale.

As a side note, if you want locale independent date formatting use the ISO sequence YYYY-MM-DD

(In reply to Gerhard Weydt from comment #3)
> If I understand Eike's answer correctly, then the slash in the second
> parameter of FORMAT doesn't mean the character "slash", but represents the
> separator between day, month and year, which is changed to the local
> separator according to the locale used,
Yes.

> and this will only work correctly,
> if you use the order of day, month and year which corresponds to the locale.
Which is the bug here.

> Indeed, Format(Now(), "DD/MM/YYYY") yields 13.04.2017 with german locale,
> whereas Format(Now(), "MM/DD/YYYY") yields 04/13/2017, with correct numbers,
> but another separator.
? I get 04.13.2017 (i.e. separator adapted but the wrong order).
Comment 6 Commit Notification 2017-04-18 15:04:22 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=51478cefaa4e265b42e3f67eda0a64767ff3efba

Resolves: tdf#107012 follow date order of the target locale

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2018-08-17 06:26:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a4c9e4539ee82c409add42e68329d4eb1100d62f

Do not convert YMD date order to YDM and vice versa, tdf#107012 follow-up

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2018-08-22 07:23:51 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7ce8689eaa9cab85193d031b911331c9675b77bc&h=libreoffice-6-1

Do not convert YMD date order to YDM and vice versa, tdf#107012 follow-up

It will be available in 6.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2018-08-22 07:34:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=16ee6373bab9056650cf3b7a153e832d8d09fc28&h=libreoffice-6-0

Do not convert YMD date order to YDM and vice versa, tdf#107012 follow-up

It will be available in 6.0.7.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.