Azure Logic Apps SQL Connector strikes again
Working with Azure Logic Apps SQL Connector can be tricky sometimes, especially if you work with datetime and OData queries.
The problem
I was fetching rows from a specific table, based on their timestamp. So I wrote a simple OData query: change ge 2020-05-12T15:17:43.1729282Z. And to my surprise it didn’t work.
The mystery
The most annoying part of this problem was that I didn’t know what went wrong. I received following error: “BadRequest. Http request failed: the content was not a valid JSON.”
Wait a minute… Did I send the invalid JSON or I received invalid JSON? Who knows. So anyway I started experimenting and noticed, that when I stop sending the OData query, it returns correct JSON. So it must be it right?
The solution
And truly, you cannot compare datetime(s) this way. Or at least not when using Azure Logic Apps SQL Connector. My query had to be adjusted to a little bit ugly format.
Old Query: change ge variables('Timestamp')
New Query: (year(change) gt year(@{variables('Timestamp')}) or ((year(change) eq year(@{variables('Timestamp')})) and (month(change) gt month(@{variables('Timestamp')}) or ((month(change) eq month(@{variables('Timestamp')})) and (day(change) gt day(@{variables('Timestamp')}) or ((day(change) eq day(@{variables('Timestamp')})) and (hour(change) gt hour(@{variables('Timestamp')}) or ((hour(change) eq hour(@{variables('Timestamp')})) and (minute(change) ge minute(@{variables('Timestamp')}))))))))))
As you can see I had to compare every part separately and that solved my issue.
The main idea of the query
Compare first part
- if greater –> return true
- if equals –> compare the rest recursively
- otherwise –> return false
Comments
Pete
This approach will fail in many cases. Using your original criteria: change ge 2020-05-12T15:17:43.1729282Z
If the change occurred in the future at 2020-07-12T11:00:00 then it will fail on the hour & min check, unless I am missing something here?
Jan Skála
Hi Pete, Yes you are right. I forgot to update this blog post. I had to eventually create a much more complicated query. I created an CLI tool for building it https://gist.github.com/skalahonza/3a2e8c497c9b3a5e72a8f8803e3da6f6
The main idea is that if year is >= return TRUE else if year is == then everything else has to be >= and so on otherwiste return False
Amine
Thank you for the tip, it worked for me for this case: Fetching data from SQL to Dataverse to get clients entered 24hours earlier in the SQL database (On-promises)
To submit comments, go to GitHub Discussions.