How to convert a date attribute format

SQL++
1

I have a date attribute in document in the below format
2016-09-23T18:48:11.000+0000
I need to convert this fomat into “YYYY/MM/DD”

Is there any method to convert so?

Thanks in advance.

2

See DATE_PART and string functions, as well as string concatenation.

3

Hi @NIVEDASIVAPRAKASAM,

What version are you using? N1QL has a new function DATE_FORMAT_STR() in the latest release 4.6 (to be released next week, DP available) that can covert dates from one supported format to another.

SELECT  date_format_str("2016-09-23T18:48:11.000+00:00", "1234-12-12" ) ;
  "results": [
        {
            "$1": "2016-09-23"
        }
    ],

Date functions and formats (ISO8601) supported in 4.5.x are at:
https://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/datefun.html

Note that your date string is not one of the supported formats. Timezone offset should be in the format “hh:mm”, you have it like “hhmm”.

Currently, in order to switch between the different date formats, you will need to do one of the following:

  1. MILLIS_TO_STR( MILLIS( your_date ), fmt )

    SELECT MILLIS_TO_STR( MILLIS(“2016-09-23T18:48:11.000+00:00” ), “1234-12-12” )
    “results”: [
    {
    “$1”: “2016-09-23”
    }
    ],

Also note that, currently the ‘fmt’ arg takes valid date in numbers (see the docs). For ex: any other invalid fat string such as “yyyy-mm-dd” or “1234-14-12” wouldn’t work, and return the full date/time string.

  1. Use SUBSTR(). This is just a string function, so your date format does’t matter.

    SELECT substr(“2016-09-23T18:48:11.000+00:00”, 0,10 ) ;
    “results”: [
    {
    “$1”: “2016-09-23”
    }
    ],

  2. Concatenate various components/parts of the date together into the required format.

    SELECT to_string(date_part_str(“2016-09-23T18:48:11.000+00:00”, “year” )) || “-” ||
    to_string(date_part_str(“2016-09-23T18:48:11.000+00:00”, “month” )) || “-” ||
    to_string(date_part_str(“2016-09-23T18:48:11.000+00:00”, “day” ));
    “results”: [
    {
    “$1”: “2016-9-23”
    }
    ],

-Prasad

3 Likes
4

Thanks @prasad!!!
That really helped me…

Second method worked out for me. :slight_smile:

5

Is there a method to convert “22-10-2017 01:25:36+0000” to “2017-10-22 01:25:36+0000”?

6

There is no straight forward function but you can use SUBSTR and concat like below

select SUBSTR(date,6,4)||SUBSTR(date,2,4)||SUBSTR(date,0,2)||"T"||SUBSTR(date,11,11)||":"||SUBSTR(date,22,2) LET date="22-10-2017 01:25:36+0000" ;

7

Hi , I have something similar requirement, I have to get the count of items for the last 20days ,group by date and in desc order , but unable to format the date
Date format I have is - “startDate”: “2020-10-09T00:00:00.000”
Tried using SUBSTR, DATE_PART(), DATE_FORMAT_STR() but no luck.
select startDate , count(*) as itemscount from mybucket AS d
LET startDate = DATE_FORMAT_STR(startDate,“1234-12-12”)
where startDate > ‘2020-09-20’ and startDate<‘2020-11-03’ group by startDate ORDER BY startDate desc

Requirement
Date Itemscount
2020-10- 31 20
2020-10- 30 34
2020-10-29 79
2020-20-28 46

8 
select sd AS startDate , count(*) as itemscount 
FROM  `mybucket`  AS d
LET sd =DATE_FORMAT_STR(startDate, '1111-11-11')
where sd > ‘2020-09-20’ and sd <‘2020-11-03’ group by sd ORDER BY sd desc

or do SUBSTR(startDate,0,10)

9

Hi All

Is there anyway to convert date for ‘20210414T210832+00:00’ into ‘2021-04-14T21:08:32’?

I have a date attribute in document in the below format
20210414T210832+00:00
I want to convert this format into “YYYY-MM-DD hh24:mi:ss” like ‘2021-04-14T21:08:32’

Is there any method to convert so? I tried with date_format_str but not helping.

Thank you in advance.

10 
SUBSTR(date,0,4)||"-"||SUBSTR(date,4,2)||"-"||SUBSTR(date,6,2)||"T"||SUBSTR(date,9,2)||":"||SUBSTR(date,11,2)||":"||SUBSTR(date,13,2)