Azure Cosmos DB: Incorrect syntax near 'Dec' error while executing query through stored procedure

Question

In a stored procedure that runs against the Azure Cosmos DB non-partitioned collection, I am running below select query:

var twentyMinutesBefore = new Date();
twentyMinutesBefore.setMinutes(twentyMinutesBefore.getMinutes() - 20);
var filterQuery = "SELECT TOP 40 * FROM c WHERE (c.transmissionState = 2 AND (" + twentyMinutesBefore + " > c.dateCreated.epoch)) OR c.transmissionState = 0 ORDER BY c.dateCreated.epoch DESC";

I execute the query as below:

var isAccepted = collection.queryDocuments(collectionLink, filterQuery, options, callback);

function callback(err, queryFeed, responseOptions) {
    if (err) {
        throw err; // <-- Error thrown from this line as per stack trace
    }
    // Iterate through query feed
}

I get the below error:

    "Message": "Microsoft.Azure.Documents.DocumentClientException: Message: 
{\"Errors\":[\"Encountered exception while executing function. Exception = Error:
{\\"errors\\":[{\\"severity\\":\\"Error\\",\\"location\\":{\\"start\\":63,\\"end\\":66},\\"code\\":\\"SC1001\\",
\\"message\\":\\"Syntax error, incorrect syntax near 'Dec'.\\"}]}   
    Stack trace: Error: {\\"errors\\":[{\\"severity\\":\\"Error\\",\\"location\\":{\\"start\\":63,\\"end\\":66},\\"code\\":\\"SC1001\\",\\"message\\":\\"Syntax error, incorrect syntax near 'Dec'.\\"}]}

In a Console application, I tried resolving the filter query to verify if it is properly formatted. It results as below:

SELECT TOP 40 * FROM c WHERE (c.transmissionState = 2 AND (1512593297244 > c.dateCreated.epoch)) OR c.transmissionState = 0 ORDER BY c.dateCreated.epoch DESC

When I copy this query as is and run in Cosmos DB query window, it runs fine and returns results as expected.

Not sure why it doesn't run through the stored procedure. Any idea what that 'Dec' means in the error? I don't find any such string in my stored procedure.

anhlc anhlc · Accepted Answer · 2017-12-07T02:29:37

Use twentyMinutesBefore.getTime(), without getTime() to get the UNIX epoch time, you will get the full date like "Thu Dec 07 2017 13:26:39 GMT+1100 (AUS Eastern Daylight Time)":

var filterQuery = "SELECT TOP 40 * FROM c WHERE (c.transmissionState = 2 AND (" + twentyMinutesBefore.getTime() + " > c.dateCreated.epoch)) OR c.transmissionState = 0 ORDER BY c.dateCreated.epoch DESC";

Azure Cosmos DB: Incorrect syntax near 'Dec' error while executing query through stored procedure

1 Answers