Change data format in QGIS field calculator using format_date

Question

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:

create a new field settings

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

null values

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 1

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!)

enter image description here

enter image description here

---

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...

enter image description here

enter image description here

• JGH: @Mapos thanks! A bit counter intuive to not set the field format but only the widget format... I have integrated it to the answer
• enor: @Mapos What is QGIS 4.3.9? Isn't the newest version 3.28? I feel like I'm missing something here.
• Mapos: @JGH, enor: typing error