Answered

Proper syntax for formatDateTime in power automate

(0) Share

Report

Posted on by ColdSpark

I am creating a list rows action where the list should be filtered for all rows where dateField = today. dateField is only date, no year, in mm/dd/yyyy format. When i try and set my filter to

dateField eq formatDateTime(utcNow(),'MM-dd-yyyy')

i get a syntax error.

Can anyone tell me what the correct filter is to check if a dateField = today with month/day/year only?

Thanks!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (1)

ColdSpark 28 on at

Like (0)

Report
Copy link

Link copied!

*dateField is only date, no TIME, in mm/dd/yyyy format.

Was this reply helpful? Yes No
ColdSpark 28 on at

Like (0)

Report
Copy link

Link copied!

using

dateField eq '@{utcNow('MM-dd-yyyy')}'

i can get the workflow to run without failure, but it does not return any records.

"parameters": {
        "entityName": "entity",
        "$filter": "dateField eq '03-16-2021'"
    }

cant figure out what the syntax needs to be to compare utcnow to a date only dataverse field?

Was this reply helpful? Yes No
mahoneypat 1,720 on at

Like (1)

Report
Copy link

Link copied!

You need to put the utcNow() in an expression for it to work. In my example, I used this expression successfully to filter a Dataverse date column.

utcNow('yyyy-MM-dd')

Regards,
Pat

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @ColdSpark,

I had major challenges with date only fields in Dataverse. The field is defined as "Date Only" but behind the scenes in Dataverse there was a time component still. To get my functions to return what I needed I had to do a greater than and less than for a day in future to get this to actually work. So I would do this (I know it is a pain):
start of day:
formatDateTime(startofDay(utcNow()),'MM/dd/yyyy hh:mm:ss')
beginning of tomorrow:
formatDateTime(startofDay(addDays(utcNow(),1)),'MM/dd/yyyy hh:mm:ss')

I would then query for dates in the dataverse that are >= on beginning and < ending.

I know this is a big workaround and hopefully you don't have to do it and someone has a better idea but I had to do this about 6 months ago to get mine to work.

Thanks,

Drew

Was this reply helpful? Yes No
Verified answer

Kundan Sah 45 on at

Like (1)

Report
Copy link

Link copied!

Hi,
The date fields need to be set in below format YYYY-MM-DDT00:00:00Z and for datetime it would be YYYY-MM-DDThh:mm:ssZ

Was this reply helpful? Yes No
ColdSpark 28 on at

Like (0)

Report
Copy link

Link copied!

This was it! the proper format as listed by Kundan is YYYY-MM-DDT00:00:00Z. Changed the hour to 7 to account for timezone. Final eq was dateField eq below

Was this reply helpful? Yes No
MarioOne 67 on at

Like (0)

Report
Copy link

Link copied!

Hey Guys, I have still the issues on having this simple task completed; just need a daily dump from a SharePoint list. The fix suggested does not work for me; whenever adding a rule to ODATA filter I am getting no data back.

Either I go this way:
or that way:

I get no data back. If I skip filter query, I will get something like:

what is even more interesting, even though I am getting record created only on two dates...
Any ideas? 🙂

Was this reply helpful? Yes No