2

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.

3
  • This recent question will help you with removing the time: gis.stackexchange.com/questions/382321/… – JClarkson Dec 17 '20 at 16:13
  • my previous question, as you might expect I did try that, bit it returns '0001/01/01' so while removing the time clearly not the way to do it. – Spatial Digger Dec 17 '20 at 16:16
  • QGIS passes the string parsing off to QT with doc.qt.io/qt-5/qdate.html#fromString-1 (You can get to the help from Edit/Select/Select Features by Expression/Date and Time/to_date and then description should point to the QDate::fromString help text. – Dave X Dec 17 '20 at 18:55
3

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: enter image description here

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.

1

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.

2
  • Side question, anyway to pipe these together? – Spatial Digger Dec 17 '20 at 17:52
  • You can nest them. I tried Format_date(To_date(Substr('10/12/2020 13:14:39',0,-9), format('dd/MM/yyyy')) ,'yyyy/MM/dd'). in Edit/Select/Select by Expression and the preview on the bottom showed '2020/12/10' – Dave X Dec 17 '20 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.