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

Question

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.

Answer 1

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.

Answer 2

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.