Change data format in QGIS field calculator using format_date

I´d like to change the data format in the attribute table from yyyy-MM-dd (default) to dd.MM.yy using the format_date function in the field calculator.

Using:

format_date(date, 'dd.MM.yy')

I got NULL values in the new field in the attribute table.

My create a new field settings:

Although the output review looks correct, in the new field only NULL values appear:

Choosing string as the output field type the new field contains intended values but in the wrong format. Applying to_dateexpression on the string values is invalid.

Any ideas how to solve this problem?

Answer

In theory, a date should be saved as a date - not string - and formatted on display. This format should be set at the column level, not at the cell (value) level, so outside of the field calculator expression.

One would open the layer property, find the field of interest and set the format of the Widget Display only (thanks to @Mapos and @Kazuhito for this major detail!)

---

Let's note that there is potentially a bug / limitation (QGIS 3.4.5) if you also set the field format as it works for a date being edited and in Form View, but NOT when displaying all values in the attribute table...