Converting string field into date field in QGIS

Question

In QGIS I have a layer with points which represent the fatal road accidents and the field "dates_14_D" contains their date but its type is string. I would like to convert this string field to date field but the OK button is inactive. What's the problem?

Answer 1

I'm surprised I haven't seen this before. Maybe I'm overlooking something obvious :)

Although you're using a different locale to me, you're using the same date format as I do in the UK, dd/MM/yyyy. I get a slightly different error (on QGIS 2.16.1) but it doesn't like that date format.

You can get around this by creating a short python script in the function editor tab of the expression editor.

• Go into the function editor tab in the expression editor
• Create a new function (New file button)
• paste the following into the code window. You may get indentation errors, so manually re-indent with spaces if needed
• click on Load button to save the changes
• switch back to the expression tab
• look under the python heading, should now see a function called parse_date_dmy

---

from qgis.core import *
from qgis.gui import *
from PyQt4.QtCore import QDate

@qgsfunction(args="auto", group='Python')
def parse_date_dmy(fromval, feature, parent):
    return QDate.fromString(fromval, 'dd/MM/yyyy')
    

If you use QGIS 3.# and get an error about PyQt4, then instead use PyQt5, i.e. replace line 3 with

...
from PyQt5.QtCore import QDate
...

You can then enter an expression like so, using your field name :

parse_date_dmy("mydate") 

If all is well, you should see something like this...

Answer 2

I don't know what it was like back in 2016, but as of QGIS 3.10, this is implemented in in the available functions in QGIS in the expression editor or the Field calculator. The documentation on the right (in the field calc dialog) gives you the solution of:

to_date("MyDateFieldNameWithDatesAsStrings" , 'dd/MM/yyyy')

and hit Ok. Adapt the "format" to the one present in your attribute table. For example, if your dates look something like 07.3.03, the correct format for that would be dd.M.yy*. Pay attention to capitalization, as the symbol m stands for "minutes" and M stands for "months".

This is not a format anybody should use. If you get a file with that format, please talk to the sender's superior. This will not stand!

Answer 3

An update for QGIS 3 of the crystal clear answer by @Steven Kay

You can get around this by creating a short Python script in the function editor tab of the expression editor.

• Go into the function editor tab in the expression editor
• Create a new function (New file button)
• paste the following into the code window. You may get indentation errors, so manually re-indent with spaces if needed
• click on Load button to save the changes
• switch back to the expression tab
• look under the python heading, should now see a function called parse_date_dmy

---

from qgis.core import *
from qgis.gui import *
from PyQt5.QtCore import QDate

@qgsfunction(args="auto", group='Python')
def parse_date_dmy(fromval, feature, parent):
    return QDate.fromString(fromval, 'dd/MM/yyyy')      
            

You can then enter an expression like so, using your field name :-

parse_date_dmy("mydate") 

If all is well, you should see something like this...

Answer 4

Or simply use :

to_date( regexp_substr( "MAJ" , '/(\\d+$)' ) || '-' || regexp_substr( "MAJ" , '/(\\d+)/' ) || '-' || regexp_substr( "MAJ" , '^(\\d+)/' ) )

With MAJ as your date field (dd/MM/YYYY).

Answer 5

Workaround-----you are unable to edit this (your error message above) until you save into a GeoPackage, per Why can't I edit attribute table imported through 'text as layer'