How to format the date in MM/DD/YYYY format

SQL++
1

Hi
we are storing date in ‘2017-09-15 11:19:00’ format and we need to convert this into MM/DD/YYYY format.

i have checked the DATE_FORMAT_STR function but it will convert to YYYY-MM-DD format.

2

Hello @pgowda,

There is not a helper function to do this today, there is a open improvement for it: MB-22551

The only formats supported are described in the documentation .

In the mean time you could use SUBSTR:

SELECT SUBSTR(date,5,2)|| '/'||SUBSTR(date,8,2)|| '/' ||SUBSTR(date,0,4) as `MM/DD/YYYY` LET date="2017-09-15 11:19:00";

The output would be:

[
  {
    "MM/DD/YYYY": "09/15/2017"
  }
]
3

DATE_PART_STR() will give you individual parts and you can then put them together in the way you want.
https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html