11

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? I use QGIS. Look at the below pictureenter image description here

  • 1
    What does it say when you click on "more info"? – Matthias Kuhn Sep 28 '16 at 12:36
  • I think that the date format is wrong; QGIS expects something like 'YYYY-MM-DD'. – ArMoraer Sep 28 '16 at 12:41
  • Parse Error: Eval Error: The conversion " % 1 " was not possible. – ilias m Sep 28 '16 at 12:43
  • I converted the format like @ArMoraer said an I repeated the procedure but again the OK button is inactive. – ilias m Sep 28 '16 at 13:01
15

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

enter image description here

| improve this answer | |
  • Thanks a lot for your detailed answer. It worked. But, for some dates, the outcome was null. Is there any explanation? – ilias m Sep 28 '16 at 20:49
  • 1
    try d/M/yyyy as your format string (see Qt date format string spec, although note that this is a Qt5 page). I tested it on days and months with single and double figures and it seemed to work OK. If that works I'll update my answer. – Steven Kay Sep 28 '16 at 21:05
  • The first time I run this procedure, the format of the date was d/M/yyyy. I have tried to run this with the format d/M/yyyy again and the outcome of some records was null as it above mentioned. In addition, I run this with the format dd/MM/yyyy and the outcome was that all the records were null. Have I done sth wrong? – ilias m Sep 28 '16 at 22:13
3

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

(whitespace are optional) 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!
| improve this answer | |
1

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

| improve this answer | |
1

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

enter image description here

| improve this answer | |

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.