0

I am using QGIS Essen version 2.14.1, and I am trying to select specific data according to dates in the field calculator:

I am using the following formula but the system is telling me: syntax error, unexpected LE

Here is my formula:

case when 
( "Copy of _4"  > 01/09/2016 and  "Copy of _5" < 18/09/2016)
 then 'Y' else 'N' end

Anyone know where I am going wrong?

  • What data types are your fields? – karpfen Jul 22 '16 at 8:38
  • 1
    Related post (by same author): Between operator QGIS. Date format should be YYYY-MM-DD in string format (just tested on QGIS 2.16). – ArMoraer Jul 22 '16 at 8:39
  • The date fields are strings actually. But it seems the query is wrong also? – philippe Jul 22 '16 at 8:39
  • If "Copy of _4" is of type text, then your error comes from the fact that you are trying to compare a text and a date, which is impossible. You'll have to use the to_date function to convert "Copy of _4" to date type. – ArMoraer Jul 22 '16 at 8:46
  • Ok great thanks! And how would I put that in my function? – philippe Jul 22 '16 at 9:11
0

I post my response to your comment as a real answer for readability purpose.

Your function should probably look like:

case when 
( to_date("Copy of _4") > '2016-09-01' and to_date("Copy of _5") < '2016-09-18')
 then 'Y' else 'N' end

This assumes that both "Copy of _4" and "Copy of _5" contain dates with the following format: YYYY-MM-DD (it probably won't work otherwise).

| improve this answer | |
0

Slightly improved version of ArMoraer's answer:

CASE WHEN
to_date(substr("Copy of _4", 7,5) + '-' + substr("Copy of _4", 4,2)  + '-' + substr("Copy of _4", 1,2)) > to_date('2016-09-01')
AND
to_date(substr("Copy of _5", 7,5) + '-' + substr("Copy of _5", 4,2)  + '-' + substr("Copy of _5", 1,2)) > to_date('2016-09-18')
THEN 'y' ELSE 'N' END

Be aware that this is rather messy and only works for the exact date format you're using 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.