How to compare dates stored in document in mm-dd-yyy format using N1QL

SQL++
1

I want to update the array element based on the lstSmtDt stored in MM-dd-yy format:

N1QL query i am using :slight_smile:
UPDATE B234AMS set opldCns= ARRAY a for a in opldCns when a.lstSmtDt < ‘05-21-17 07:56:34’ END where docType=‘svcPntStoAd’
this query shoud update array and delete array element having lstSmtDt >‘05-21-17 07:56:34’
but this is not working as in N1QL supported format is yy-mm-dd so how can i rewrite my query so that it works without changing date format in database. Is there any way to convert this date using N1QL and use that in update query.

Array:
“opldCns”: [
{
“bpyAtnNa”: “”,
“bpyCoNa”: “JOHN MAPLES”,
“lstSmtDt”: “08-21-17 07:56:34”,
“pkgQy”: 1
},
{
“bpyAtnNa”: “”,
“bpyCoNa”: “JOHN MAPLES”,
“lstSmtDt”: “07-20-20 07:56:34”,
“pkgQy”: 1
},
{
“bpyAtnNa”: “”,
“bpyCoNa”: “JOHN MAPLES”,
“lstSmtDt”: “06-19-18 07:56:34”,
“pkgQy”: 1
}
]

2

N1QL/JSON supports date as ISO-8601 formatted string and easily string comparable and has lot of supported functions https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html.

You have two options:

  • store date as ISO-8601 format string
  • Convert into ISO-8601 format on fly

Try following:

UPDATE B234AMS AS d
SET d.opldCns= ARRAY v FOR v IN d.opldCns
     WHEN ("20"||SUBSTR(v.lstSmtDt,6,2)||"-"||SUBSTR(v.lstSmtDt,0,5) || "T"||SUBSTR(v.lstSmtDt,9,8)) <
          "2017-05-21T07:56:34" END
END WHERE d.docType="svcPntStoAd";
3

Thank you so much it worked!!