2

I merged 2 layers in QGIS and am trying to clean up the attribute table using the Field Calculator. One layer has 1 column called Date in the format YYYY-MM-DD. The other layer has 3 separate columns with real numbers for Month, Day, and Year. I'd like them all to be in the YYYY-MM-DD format, preferably using the Field Calculator. I'm not experienced with Python and don't know where to begin with the expression builder. So here's an example of each type of date format within the same layer.

Month, Day, Year all in separate columns Month, Day, Year all in separate columns

And this is how I want all of them to look (YYYY-MM-DD) This is how I want all of them to look (YYYY-MM-DD)

Improve this question
2
  • This Date field and the three others refer to the same date? Could you post an example?
    – gvanhavre
    Commented Sep 4, 2020 at 21:39
  • I posted an example, hopefully that will help!
    – mjf823
    Commented Sep 4, 2020 at 23:12

1 Answer 1

Reset to default
2

You can clean up the attribute table using the Field Calculator in the next way:

Update your fields using the following expressions:

# Date to year, mount, day
year("Date")
month("Date")
day("Date" )

# day, month, year to date
to_date(to_int("Day") || '/' ||  to_int("Month")  || '/' ||  to_int("Year"),'d/M/yyyy')

To calculate the date you can use the following function

to_date(string[,format][,language])

To have more information about the date format you can check https://doc.qt.io/qt-5/qdate.html#fromString-1

enter image description here

Calculate the month from the date:

enter image description here

Improve this answer
2
  • The OP has a mix of records, some with month, day, year, some with a date. This answer shows how to convert one to the other, but not how to selectively convert only the month, day, year to date. That requires use of a conditional on the date being null - if ("Date" is null ... This could be used to replace the original date field, but the actual syntax depends on the type of the date field (date vs. string).
    – Llaves
    Commented Sep 6, 2020 at 16:20
  • Yes, it is true. The expression can be adjusted to take into account all the particularities of the data. The good thing is that QGIS provides all the expressions needed to do the conversion
    – Leo Cardona
    Commented Sep 7, 2020 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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