Cosmos db sql api query with datetime filter in WHERE condition

Question

Trying to get those documents whose modified date(datetime) is equal to (currentdate -7) but NOT working, pseudocode as below

"SELECT * FROM c where c.LastModifiedDate = (GetCurrentDate-7)"

How to do it using cosmosdb sql api? without udf because we have timer trigger based Azure function which querying Cosmos DB using above SQL query directly when it get triggered each time and binding results to a parameter of the function.

    [FunctionName("TimerCosmosDbWriteExample")]
    public static async Task Run([TimerTrigger("0 */5 * * * *")]TimerInfo myTimer,
        TraceWriter log,
        [CosmosDB(
            databaseName: "Database",
            collectionName: "Collection",
            ConnectionStringSetting = "MyConnectionString",
            SqlQuery = "SELECT * FROM c where c.LastModifiedDate = GetCurrentDate-7")] IEnumerable<Entity> documents)
    {
     //function body -- loop through retrieved documents & process it 
    }

Is there that much of a difference between using the [DocumentDb] parameter attribute vs just initializing the Cosmos Client in the function and executing that same code? That way you can at least manipulate the date and parameters. I sadly don't know an easier way to do it. — Mark C.
Take a look at this article here that talks about working with dates in Cosmos DB. docs.microsoft.com/en-us/azure/cosmos-db/working-with-dates — Mark Brown

Jay Gong Jay Gong · Accepted Answer · 2020-01-13T02:10:38

I have 2 viewpoints for your reference:

1.Since you invoke the sql query in azure function, you could calculate the currentDate-7 with code,then pass it into query sql as an parameter.It's a common usage and just match the format of LastModifiedDate column.

2.I don't know why you said you can't use udf with sql query in azure function,it doesn't make sense. As i know,UDF is part of SQL.Please refer to my previous case:Query a specific time range data from cosmos db and store it in sql database via azure data factory

Cosmos db sql api query with datetime filter in WHERE condition

1 Answers