How to change ISO date format to string format("DD/MM/YYYY") in Couchbase query?

Question

I have a date attribute in document in the below format

 2016-09-23T18:48:11.000+00:00

I need to convert this format into below format

“DD/MM/YYYY”

Is there any method to convert so?

I tried the below query :-

SELECT  date_format_str("2016-09-23T18:48:11.000+00:00", '11-11-1111' ) ;

Result :-

[
    {
        "$1": "2016-09-23T18:48:11Z"
    }
]

Expected Result :-

"23/09/2016"

Thanks in advance.

Answer 1

Can be accomplished with DATE_FORMAT_STR

For your example;

SELECT DATE_FORMAT_STR("2016-09-23T18:48:11.000+0000", "DD/MM/YYYY") AS formatted_date

For more information; https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#fn-date-format-str

Answer 2

It depends on the Server version

Check various tabs

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#date-string

SELECT DATE_FORMAT_STR("2016-09-23T18:48:11.000+0000", "%d/%m/%Y") AS formatted_date;

SELECT DATE_FORMAT_STR("2016-09-23T18:48:11.000+0000", "DD/MM/YYYY") AS formatted_date;

If you get error that your version may not supported. You can use as follows

SELECT CONCAT2("/",SUBSTR(date,8,2), SUBSTR(date,5,2), SUBSTR(date,0,4)) AS newdate LET  date = "2016-09-23T18:48:11.000+0000";