Selecting all records from the last three full days in Azure Cosmos DB

Vimal Patel 21 Reputation points
2020-11-21T17:30:19.763+00:00

I'm looking to retrieve records from the last 3 full days.

How would I be able to amend the below, so that it does not bring back any records from today, nor any historical partial day?

SELECT * FROM c WHERE (c["createdAt"] > DateTimeAdd("dd", -3, GetCurrentDateTime ()))

Thanks very much in advance.

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,431 questions
0 comments No comments
{count} votes

Accepted answer
  1. Anurag Sharma 17,566 Reputation points
    2020-11-23T04:58:08.947+00:00

    Hi @Vimal Patel , welcome to Microsoft Q&A forum.

    If we directly use the GetCurrentDateTime() function it will return the time stamp as well and your query would not work for full day depending upon time. You can use below query. Here we are forming the date part and then comparing it with the date property.

    SELECT * FROM c WHERE (c["createdAt"] > DateTimeAdd("dd", -3, DateTimeFromParts(DateTimePart("YYYY", GetCurrentDateTime ()), DateTimePart("MM", GetCurrentDateTime ()), DateTimePart("DD", GetCurrentDateTime ())))) and   
    c["createdAt"] <  DateTimeFromParts(DateTimePart("YYYY", GetCurrentDateTime ()), DateTimePart("MM", GetCurrentDateTime ()), DateTimePart("DD", GetCurrentDateTime ()))  
    

    Its a long query and to shorten it we can also create a UDF and call it to do the same.

    Please let me know if this helps or else we can work further to get it resolved.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Vimal Patel 21 Reputation points
    2020-11-23T19:16:14.173+00:00

    Huge thanks @AnuragSharma-MSFT that works very well.

    Though I've just learned that its a very expensive query versus simply getting all the data, rather than partial data..

    Thanks again,

    Vimal.

    0 comments No comments