Filter is not working in Dataflow in ADF

Question

We are looking to get 1year data from current date based on PurchaseDocumentCreatedDateTime column in dataflow. .Below expression is not working.

toTimestamp(PurchaseDocumentCreatedDateTime) >= toTimestamp(addDays(currentDate('UTC'),-365), 'yyyy-MM-dd HH:mm:ss.SSS','en-US')

We have tried with sampling data and data is available but filtering is not working. when we use below expression and in datapreview we can able to see some data.

toTimestamp(PurchaseDocumentCreatedDateTime) <= toTimestamp(currentDate())

So, it looks like even data is available filter is not working to filter data for 1year.Could you please suggest on this

Answer 1

@siva prasad kukati Welcome to Microsoft Q&A forum and thanks for reaching out here.

In your expression **toTimestamp(PurchaseDocumentCreatedDateTime) >= toTimestamp(addDays(currentDate('UTC'),-365), 'yyyy-MM-dd HH:mm:ss.SSS','en-US')** , assuming PurchaseDocumentCreatedDateTime is of type string and I see that you are using toTimestamp() function for converting the CurrentUTC which is already in timestamp type. Please note that toTimestamp() does not expect a parameter of type 'timestamp'.

You can just get rid of the toTimestamp() function on the right hand side and it should work.

Sample below:

toTimestamp(PurchaseDocumentCreatedDateTime)  >= addDays(currentUTC(), -365)

Please note that CurrentUTC gets the current timestamp as UTC. If you want your current time to be interpreted in a different timezone than your cluster time zone, you can pass an optional timezone in the form of 'GMT', 'PST', 'UTC', 'America/Cayman'. To convert the UTC time to a different timezone use fromUTC()

I did tested the above and it works as expected.

Expression used to filter data: toTimestamp(DateTime) >= addDays(currentUTC(), -365)

Output:

Hope this helps. Let me know if you have further query.

Thanks

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.