"formatDate" returns an unexpected date-time result.

Problem:

When using the date and time pattern for formatting and parsing out the date it provides an incorrect result and returned unexpected date.

For e,g:

Query  "formatDate(_messagetime, "YYYYMMddHHmmss") as formattedDate" returns incorrect results and obtained an unexpected date.

If the message time is "2017-12-31 23:59:59.000 +0900", it returned the result incorrectly as "20181231235959" in place of "20171231235959".

Resolution:  

This unexpected date in the query is due to the wrong format used for the year.

Please use lower case yyyy for the year.

formatDate "YYYYMMddHHmmss"  should be changed to "yyyyMMddHHmmss" which should resolve the issue.

The case of the characters matters a lot, therefore following documentation link: SimpleDateFormat can help in choosing the correct format.