Compare date data in string ("MM/dd/yyyy") with between clause

SQL++
,
1

{
…
“endDate”: “12/05/2032”
…
}

I have a list of docs like this, so I want to compare this date by. greater than/less than clause or between clause. But I am not able to compare the date string. Is there any way to do this ?

2

ISO-8601 date can only string comparable https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#date-formats. You need to change date format.

Use following expression to convert into ISO-8601  format and compare

CONCAT(SUBSTR(endDate,6,4),"-",SUBSTR(endDate,0,2),"-", SUBSTR(endDate,3,2))
      OR
CONCAT2("-",SUBSTR(endDate,6,4),SUBSTR(endDate,0,2), SUBSTR(endDate,3,2))