Issues while fetching data from cosmos db using data factory

Question

Hi All,
I have been trying to fetch data from cosmos Db using a query which works well in cosmos Db explorer in Azure portal. But, the same query gives error while using it with Azure Data Factory.

Query: SELECT * FROM c where c.lastModifiedDateTime > DateTimeAdd("hh", -1, GetCurrentDateTime())

Below is the detailed error message:
One or more errors occurred.
Message: {"errors":[{"severity":"Error","location":{"start":47,"end":58},"code":"SC2005","message":"'DateTimeAdd' is not a recognized built-in function name."}]}
ActivityId: 9af4df3d-957a-4350-8348-0be910b673b2, documentdb-dotnet-sdk/2.5.1 Host/64-bit MicrosoftWindowsNT/6.2.9200.0
Exception from HRESULT: 0x800A0B00

Any help is appreciated!

Answer 1

Hi @Manish Patel ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

There are two possible approaches to implement the above. As per my understanding, in the above scenario, we would like to generate last hour datetime in UTC.

Approach 1:

To generate the datetime from ADF using variables as below

Expression in Variable :

@concat('SELECT * FROM c where c.date>"',subtractFromTime(utcnow(),1,'Hour'),'"')  

Approach 2:

To create an UDF in Cosmos DB and to use the same from ADF in query.

Ref: Issue

Hope this helps! Please let us know if the requirement is not aligned to this solution and we will be glad to assist further.

-------------------------------------------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.