to_date arranging dd/MM/yyy

Question

I have a column as a string 10/12/2020 13:14:39 I want to convert it to Date format (I'll do that using the refactor tool later). First I need to rearrange this so the time is discarded and the dd/MM/yyyy is arranged as yyyy/MM/dd.

I'm using field calculator, how do I do this?

I've tried to_date("CreatedAt", 'dd/MM/yyyy') and format_date("CreatedAt", 'dd/MM/yyyy') neither give the correct result.

Answer 1

Since you are starting with a datetime string, you can parse it fully and format the result as a plain date string in one step:

format_date(to_datetime('10/12/2020 13:14:39',
   'dd/MM/yyyy HH:mm:ss'),
   'yyyy/MM/dd')

or

format_date(to_datetime("CreatedAt",'dd/MM/yyyy HH:mm:ss'),'yyyy/MM/dd')

(Note the "Preview: '2020/12/10'" in the Edit/Select/Select by Expression window below:

The documentation for the QGIS to_data and to_datetime expression's format attribute and its parsing is in QT as https://doc.qt.io/qt-5/qdate.html#fromString-1 and https://doc.qt.io/qt-5/qtime.html#toString . You can find your way to those through the QGIS expression function description text.

Answer 2

Surely there's a better way, after a random guess I got this.

Substr("CreatedAT",0,-9)
To_date("CreatedAt", format('dd/MM/yyyy'))
Format_date("EndDate",'yyyy/MM/dd')

I'll leave this here for a while to give people the chance for a more eloquent way to do this.