OData cast function returns "The field 'cast' of the record wasn't found."

[bobesponja]

(For this post, I'm using <date_here> as a placeholder for a Date and Time column in Sharepoint referenced like this.)

@{first(outputs('LastModifiedEntry')?['body/value'])?['LatestModification']}

---

I'm trying to get the SQL connector's Get Rows (V2) action to let me filter by a datetime2 column. From what I've gathered, DateTime was removed from OData, so you have to use either Date, Time, or DateTimeOffset. After exhausting all given solutions online, I figured I could try using this in my Filter Query to try and make it do the same thing as "datetime ge <date_here>":

(date(LAST_MODIFIED_DATE) eq date(<date_here>) and time(LAST_MODIFIED_DATE) gt time(<date_here>)) or date(LAST_MODIFIED_DATE) ge date(<date_here>)

This way, it could avoid the issue that the oft-suggested "year(x) ge year(y) and month(x) ge month(y) and..." query causes (namely, that if x is "2023-08-31T00:00:00Z" and y is "2023-09-01T00:00:00Z", it will claim that x is greater than y because 31 is greater than 01). However, it looks like it's impossible to to compare two time values, so it seems to ignore them and only reference the date()s. (I tested this by trying to filter for records where "time(LAST_MODIFIED_DATE) eq time(<date_here>)" and then for "time(LAST_MODIFIED_DATE) gt time(<date_here>)" after that. The first one provided no results, and the second one just returns all records, even after subsituting gt for ge.)

I eventually found the OData cast() function. With this, I could cast LAST_MODIFIED_DATE as DateTimeOffset and leave <date_here> alone since it's already treated as that type. So I tried this query:

cast(LAST_MODIFIED_DATE, Edm.DateTimeOffset) gt <date_here>

But it gave the error in the title, "The field 'cast' of the record wasn't found.". This didn't make sense since cast() isn't a field, but a function. I wondered if maybe Filter Query somehow isn't able to recognize cast(), so I tried these two intentionally-faulty queries to test that theory:

cast(LAST_MODIFIED_DATE) gt <date_here>
cast(LAST_MODIFIED_DATE,Edm.String) gt <date_here>

The first one responded error "Cast or IsOf Function must have a type in its arguments.", and the second with "A binary operator with incompatible types was detected. Found operand types 'Edm.String' and 'Edm.DateTimeOffset' for operator kind 'GreaterThan'.", which proved that Filter Query is able to recognize cast().

But why isn't cast() being recognized when it's presented with correct syntax and type choices? It does this if I try casting <date_here> as Edm.DateTimeOffset too, or if I try to cast them both as Edm.String. I can't change the datasource to use DateTimeOffset instead of datetime2, and I can't use Execute a SQL query (V2) because my datasource is on-prem through an on-prem gateway. 

[grantjenkins]

I don't believe we can use cast within a Filter query. What is the query you are trying to achieve? Are you trying to only return dates later/earlier than a particular date (now for example), or between two dates, etc.?

[bobesponja]

I'm trying to query for dates later than LatestModification, which is equal to whatever the latest LAST_MODIFIED_TIME was when the flow last ran. So far, I've been having to use a query that relies on date(), which returns much more than is necessary.

I was hoping cast() would be an acceptable solution since time() refuses to work as expected. Apparently it's meant to at some level, since it knows how to correctly recognize when I use cast() with improper syntax (as in the one-parameter example) *and* because it apparently will successfully cast in situations where casting will cause an error (as in the Edm.String example).

[grantjenkins]

Can you confirm the exact date format that LAST_MODIFIED_DATE is returned as in your flow?

I'm not sure why you can't just do the following:

//Examples below

//LAST_MODIFIED_DATE greater than now (UTC date/time)
LAST_MODIFIED_DATE gt '@{utcNow()}'

//LAST_MODIFIED_DATE greater than 5 days ago
LAST_MODIFIED_DATE gt '@{addToTime(utcNow(), -5, 'Day')}'

//LAST_MODIFIED_DATE greater than the start of this month
LAST_MODIFIED_DATE gt '@{startOfMonth(utcNow())}'

[bobesponja]

LAST_MODIFIED_DATE is returned as "2023-11-02T14:56:16.8833333Z" in the flow. (In the original SQL table, it's a datetime2 presented as "2023-11-02 14:56:16.8833333").

I can't reliably use the 3 methods given because I'm trying to run this as efficiently as possible since the flow runs every minute (and many others follow its pattern, though some run less frequently). For context, all of these flows are trying to keep a (slightly smaller) copy of the original SQL database tables in SharePoint to deal with licensing limitations set by my superior, which are in place because of (1) high employee turnover and (2) the planned replacement of our department with a third party service, which may add delay on top of that turnover if trends continue. During slowdowns, local blackouts, Power Automate/other outages, or timeout errors (these are all rare, but still serious concerns in my environment), the flow needs to know when the most recent LAST_MODIFIED_DATE was so it doesn't miss any changes that could've been lost during that time if the "last 5 minutes" query was used. I used this approach when designing another similar series of flows that used XML over HTTP, and this approach guaranteed that the flow ran as quickly as possible while also being able to survive outages.

A recent migration to a new server and (possibly) an unexplained change in how Power Automate works seems to have increased the amount of timeouts and lag the flow experiences (it was running at 1-3s consistently, but now goes to 4-8s and sometimes has slowdowns to 40s), so keeping the query as-is right now ("date(LAST_MODIFIED_DATE) ge date(<date_here>)", which pulls everything starting from midnight on <date_here>) might increase the risk of slowdowns occurring. 

[grantjenkins]

I didn't realize you were running your flow every minute. Just a few questions.

• Why do you need to run your flows so frequently?
• How much data are you putting into your SharePoint list - if running every minute I could imagine you would end up with a lot of data and quickly start to reach limits, etc. As soon as you start to hit ~85,000 items things start to break behind the scenes.
• I'm still not sure why you are looking to move from SQL. However, if you had to move the data I'd recommend using something like Dataverse instead of SharePoint.

[bobesponja]

1. They need to run every minute to ensure accurate data is being kept. The tables are being used to support a read-only mobile version of the delivery tracking software we use at the company, since that software's developers have not made a mobile version themselves. I would prefer to use a direct connection to the SQL data base instead, but my boss prefers I use SharePoint to avoid license management overhead that comes with high employee turnover. (Since we'll be replaced with a third-party solution, we can't ensure timely responses.)

2. I've limited time-related tables to August of this year, so DORDERS is up to 8k so far. Some others are much longer, and some others would be much longer if they didn't have flows to automatically delete old entries. I'm thinking of taking the delete-old-entries approach to all tables in SharePoint since the app's functionality is currently limited to present-day shipments anways.

3. I've wanted to use Dataverse instead of SharePoint, but this carries the same problems that SQL does where we'd have to deal with licensing issues. I know Dataverse for Teams is an option, but then we'd be limited to using the Teams player.

[bobesponja]

In any case, can anyone confirm if cast() is unsupported in these queries, or if it's just a bug/undocumented constraint?