9

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

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

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

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

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

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.