Odata filter query on CDS date-only column

[sideserf]

Hi all,

I'm currently trying to use the List Records action to retrieve any CDS records where the date is less than or equal to Today, however I seem to be getting the following error:

A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'LessThanOrEqual'.

The column chc_transactiondate I'm evaluating is of Date Only format in CDS.

I've tried a few variations on the following but get the same error:

crc9f_abstatus eq 'Awaiting Input' and chc_transactiondate le '@{formatDateTime(utcnow('yyyy-MM-ddTHH:mm:ssZ'))}'

Anyone have any ideas?

Many thanks.

[v-siky-msft]

Hi @sideserf ,

The thing is that, formatDateTime function converts the data time to string type, but chc_transactiondate field is Data type field, which caused the incompatible error.

Please change to use utcnow() function directly as follows.

crc9f_abstatus eq 'Awaiting Input' and chc_transactiondate le @{utcNow()}

 Hope this helps.

Sik

[sideserf]

Hi Sik,

Thanks for your reply. I've tried your suggestion and it still gives exactly the same error.

The column chc_transactiondate is in Date Only format and not Date & Time, I'm wondering if that might be an issue?

[v-siky-msft]

Hi @sideserf ,

What is the Behavior of date only field under Advanced options in column properties?

Sik

[sideserf]

@v-siky-msft 

Here is the column configuration:

[v-siky-msft · Accepted solution]

Hi @sideserf ,

Please add a Convert time zone step before List records steps, to convert the @{utcNow()} from UTC to your local time zone in yyyy-MM-dd format.

Hope this helps.

Sik

[sideserf]

Thanks for the help @v-siky-msft . I think the issue was the single quotes around the date value was converting it it a string so causing the incompatible type error.