Date format changes when in combination with other data in report

[pauly]

Hello there. Today I came across a strange thing that I am really puzzled, that the date format changes when in combination with other data in a formatted field in report.

For example, in a Formatted field, with a single Data field =BirthDate, in this case [BirthDate] displayed correctly, like I set in the table: 30.12.1999.

But if the BirthDate combines with other data, for example in a formatted field, the Data field =[FirstName]&" “&[LastName]&”, “&[Gender]&”, nar. "&[BirthDate], then the [BirthDate] displays a very long date EVEN with time, namely: Saturday, 30 December 1999, 0:00:00 Central European Standard Time.

How to make the date display simply like 30.12.1999 at the latter case? Thanks!!!

PS: After two weeks intensive learning, I want to say, that LO Base is great, better than Access in many ways, and I am really grateful to this friendly comunity.

(LO 7.1.5.2, Mac OS 11.5.2, HSQLDB)

[Ratslinger]

Hello,

I cannot say why the date is placed in that format and not in another, but it is because you are concatenating data and there is no field formatting when this is done. With other fields you can format the output.

Create the report based upon a query and include a field using To_Char:

TO_CHAR("BirthDate", 'DD.MM.YYYY' )

Then use that in the concatenation.

Edit:

You can also use this:

DAY([BirthDate])&"."&MONTH([BirthDate])&"."&YEAR([BirthDate])

in the concat instead of [BirthDate]

[pauly]

Thank you Ratslinger! They both work perfectly. I like the second one more, though the TO_CHAR function is very powerful. Thanks a lot!!!

[pauly]

Hello,

when I use DAY([BirthDate]) and MONTH([BirthDate]), they work fine except they only return day and month numbers without the leading zeros. How to do to make the day and month number with leading zeros? Like day 1-9 will be 01, 02 … 09. and month 1-9 will be 01, 02 … 09. Thanks a lot.

[Ratslinger]

@pauly

How to do to make the day and month number with leading zeros?

RIGHT("0"&DAY([BirthDate]);2)

and

RIGHT("0"&MONTH([BirthDate]);2)

[pauly]

Thank you so much Ratslinger, that works like a charm.