cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kosenurm
Super User
Super User

Date only Dataverse column filter rows to today

Hi there, 

 

I have a dataverse table which has a Date Only column which is patched from a Power App. So it has dates in it such as 05/16/2022 which is today and other dates.

 

What I want is to filter rows to only those of today

 

I have tried 

startdate eq formatDateTime(utcNow())

 

but I get error of A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'Equal'.

 

Any ideas?

 

Thanks

K.

 

5 REPLIES 5
Expiscornovus
Super User
Super User

Hi @Kosenurm,

 

Can you check the field names in your output when not filtering? I assume it has a prefix.

 

prefix.png

 

Try something like below (replace my prefix crca4_ by yours):

crca4_startdate eq '@{utcNow('yyyy-MM-ddT00:00:00Z')}'

 

filterquery.png

Thanks for your reply. 

 

When I try 

@{utcNow('MM/dd/yyyyT00:00:00Z')}
as an expression, it says it is invalid??
 
Thanks
K.
Expiscornovus
Super User
Super User

Hi @Kosenurm,

 

Did you copy/paste the code snippet directly as text? Because the  '@', '{' & '}' characters are around the expression you do not need to use the expression editor.

 

@{yourexpression}

 

 

However, when you want to add the UtcNow function via the expression editor you could simply use this instead:

 

utcNow('yyyy-MM-ddT00:00:00Z')

 

 

Thanks. I am still getting the error of "A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'Equal'."

 

The columns are Date Only columns with behaviors of Date Only. 

 

The previous List Rows action before this one, is correctly displaying the single row we have in the Dataverse at the moment of "cr2a7_startdate""2022-05-16",

 

Any ideas?

 

Thanks

K.

Expiscornovus
Super User
Super User

Hi @Kosenurm,

 

Thanks for checking. Another thing to double check. Did you use single quotes around the date value/expression with the utcNow function in your filter query?

 

If that is still not working maybe our configuration is slightly different? I also used a Date only field in my test. Below is a screenshot of that setup.

 

dateonly_field.png

 

I used a Compose action with the value field directly after the first List Rows to check the values.

    "crca4_startdate@OData.Community.Display.V1.FormattedValue": "5/16/2022",
    "crca4_startdate@odata.type": "#DateTimeOffset",
    "crca4_startdate": "2022-05-16T00:00:00Z",

 

I used the third field for the Filter Query, which has the yyyy-MM-ddT00:00:00Z format. So, although it is a date only it was still storing the timestamp as well. But that would always be the same.

 

Alternatively, you could try and use that formattedvalue field instead?

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

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

Users online (1,620)