cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sideserf
New Member

Odata filter query on CDS date-only column

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

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.

 

Snipaste_2020-12-07_10-09-21.png

Snipaste_2020-12-07_10-11-17.png

Hope this helps.

Sik

View solution in original post

6 REPLIES 6
v-siky-msft
Community Support
Community Support

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

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
Community Support
Community Support

Hi @sideserf ,

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

Sik

@v-siky-msft 

Here is the column configuration:

 

sideserf_0-1607078857371.png

 

v-siky-msft
Community Support
Community Support

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.

 

Snipaste_2020-12-07_10-09-21.png

Snipaste_2020-12-07_10-11-17.png

Hope this helps.

Sik

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.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Users online (3,216)