RE: Rest API date+time + sorting

  1. When querying the lastModifiedDate or createdDate from the Rest API, how do you get the time part – it just seems to return the date.  And how do you get it to show up in ISO8601 format?

For example

/services/rest/query/v1/suiteql

{
    “q”:”SELECT Id, lastModifiedDate from Item”
}
returns records like
{“links”:[],”id”:”26″,”lastmodifieddate”:”7/3/2019″}
why is the lastmodifieddate not in a date/time format?
2) When trying to sort by lastModifiedDate, an error comes (but without the order by clause it works…or using some other fields in order by it works).
Example
{
    “q”:”SELECT Id, lastModifiedDate from Item order by LastModifiedDate asc”
}
returns
{“type”:”https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.5.1″,”title”:”Internal Server Error”,”status”:500,”o:errorDetails”:[{“detail”:”An unexpected error occurred. Error ID: kg79hg1f15gm0xxrqjikt”,”o:errorCode”:”UNEXPECTED_ERROR”}]}
rshariff Rookie Asked on October 12, 2020 in SuiteTalk.
Add Comment
1 Answers

Use TO_CHAR to format your date as a string in the format you want.

Intermediate Answered on October 12, 2020.

So that gets the lastModfiiedDate in the result set in the right format, but I still can’t sort by it.

 

{
    “q”:”SELECT Id, to_char(lastModifiedDate, ‘yyyymmddhh24miss’) as lastModifiedDate from customer order by lastModifiedDate”
}
returns
{“type”:”https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1″,”title”:”Bad Request”,”status”:400,”o:errorDetails”:[{“detail”:”Invalid search query. Detailed unprocessed description follows. Search error occurred: Invalid or unsupported search.”,”o:errorQueryParam”:”q”,”o:errorCode”:”INVALID_PARAMETER”}]}
but changing lastModifiedDate to something else in the order by doesn’t return an error.  I even tried
{
    “q”:”SELECT Id, to_char(lastModifiedDate, ‘yyyymmddhh24miss’) as lastModifiedDate from customer order by to_char(lastModifiedDate, ‘yyyymmddhh24miss’) desc”
}
on October 13, 2020.

You are on your own for the sorting, I haven’t gotten that to work.

on October 13, 2020.

Thanks – one final question.  Are the table + field names as specified via SQL in REST the same as the ones that are returned via the metadata-catalog?  For some reason I am able to query by certain tables in REST SQL (like the “item” table), but it doesn’t show up in the metadata-catalog list of objects.

 

For example

GET /services/rest/record/v1/item

returns

{
    “type”: “https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.5”,
    “title”: “Not Found”,
    “status”: 404,
    “o:errorDetails”: [
        {
            “detail”: “Record type ‘item’ does not exist.”,
            “o:errorUrl”: “/services/rest/record/v1/item”,
            “o:errorCode”: “INVALID_VALUE”
        }
    ]
}
BUT
POST /services/rest/query/v1/suiteql
{
    “q”:”SELECT Id, to_char(lastModifiedDate, ‘yyyymmddhh24miss’) as lastModifiedDate from item”
}
returns valid records back
on October 13, 2020.
on October 13, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.