N1Q1 Date compare is not working

SQL++
1

Hi,
I want to update an attribute’s status of my couchbase json document based on the date ; if the date is less than my desired date the attribute’s status should be changed .

Currently using this Query but it is not working and date format is DD-MM-YYYY
update test set recordstatus= “1”
WHERE (SUBSTR(UpdateDate,0,2)||"-"||SUBSTR(UpdateDate,3,2)||"-"||SUBSTR(UpdateDate,6,4)) <
“18-12-2019” AND
doctype=“Document1”;

Thanks in advance

2

JSON dates are string comparable when stored in iSO-8601 format https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html

   UPDATE  `test`  SET recordstatus= "1"
    WHERE   (SUBSTR(UpdateDate,6,4)||"-"||SUBSTR(UpdateDate,3,2)||"-"||SUBSTR(UpdateDate,0,2)) <
    "2019-12-18" AND doctype="Document1";