Filter Dataverse List Rows by date range

(0) Share

Report

Posted on by CU04061849-0

Can anyone please see what I am doing wrong here? I get this error: Action 'List_rows' failed: Error reading JObject from JsonReader. Path '', line 0, position 0.

it works for this filter: sp_city eq 'milledgeville' and sp_coreprovinserteddt ne null

tried with and without single quotes, using logical name correctly

and here is the raw output:

{
    "host": {
        "connectionReferenceName": "XXXXXXXXXXXXXXXXXXXXXXX",
        "operationId": "ListRecords"
    },
    "parameters": {
        "entityName": "XXXXXXXXXXXXXXXXXXXXXXXXXXXX",
        "$select": "sp_legalname, sp_coreprovinserteddt",
        "$filter": "sp_coreprovinserteddt gt '2026-04-15T20:01:27.0052954Z' and sp_coreprovinserteddt lt '2026-04-22T20:01:26.9622610Z'",
        "$top": 5000,
        "accept": "application/json;odata.metadata=minimal"
    }
}

Variables:

thank you!

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (0)

Sort by

Suggested answer

Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

When you filter on a string like city name you put the value in single quotes. But when you filter on a date you just use the ISO 8601 format for the date without single quotes. Try the following filter instead of yours

sp_coreprovinserteddt gt 2026-04-15T20:01:27.0052954Z and sp_coreprovinserteddt lt 2026-04-22T20:01:26.9622610Z

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
11manish 3,333 on at

Like
a
(1)

Report
Copy link

Link copied!

The issue is caused by wrapping DateTime values in single quotes. In Dataverse OData filters, DateTime values must be unquoted. Removing the quotes will fix the error.

try like below :

sp_coreprovinserteddt gt 2026-04-15T20:01:27.0052954Z and sp_coreprovinserteddt lt 2026-04-22T20:01:26.9622610Z

or

sp_coreprovinserteddt gt @{formatDateTime(variables('startDate'),'yyyy-MM-ddTHH:mm:ssZ')}

Was this reply helpful? Yes No
CU04061849-0 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the suggestions. Pstork1 11manish

both of these didn't work for me:
Here is no single quotes:

Same error- Action 'List_rows' failed: Error reading JObject from JsonReader. Path '', line 0, position 0.

{

    "host": {

        "connectionReferenceName": "shared_XXXXXXXXXXXXXXXXX",

        "operationId": "ListRecords"

    },

    "parameters": {

        "entityName": "sp_vw_XXXXXXXXXXXXXXXXXXXXXXX",

        "$select": "sp_legalname, sp_coreprovinserteddt",

        "$filter": "sp_coreprovinserteddt gt 2026-04-16T15:58:06.6492840Z and sp_coreprovinserteddt lt 2026-04-23T15:58:06.5799872Z",

        "$top": 5000,

        "accept": "application/json;odata.metadata=minimal"

    }

}

-----------------------------------------------------------------------------------

{

    "host": {

        "connectionReferenceName": "shared_xxxxxxxxxxxxxxxxx",

        "operationId": "ListRecords"

    },

    "parameters": {

        "entityName": "sp_vw_xxxxxxxxxxxxxxxxxxxxxx",

        "$select": "sp_legalname, sp_coreprovinserteddt",

        "$filter": "sp_coreprovinserteddt gt 2026-04-16T16:04:17Z and sp_coreprovinserteddt lt 2026-04-23T16:04:17Z",

        "$top": 5000,

        "accept": "application/json;odata.metadata=minimal"

    }

}

Error: Action 'List_rows' failed: Error reading JObject from JsonReader. Path '', line 0, position 0.

Was this reply helpful? Yes No
Suggested answer

Kalathiya 2,456 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hello @CU04061849-0,

You need to add the single quote in expression.

Example:

createdon ge '@{formatDateTime(addDays(utcNow(),-95),'yyyy-MM-ddTHH:mm:ssZ')}' and createdon le '@{formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ssZ')}'

If this response resolves your issue, please mark it as the Verified Answer so it can help other community members as well.

---------------------------------------------------------------------------------

📩 Need more help? Just mention @Kalathiya and I’ll be happy to assist.

✔️ If this answer helped you, please tick “Does this answer your question?” so it can be marked as the Verified Answer.

💛 A Like always motivates me to keep contributing!

Was this reply helpful? Yes No
CU04061849-0 13 on at

Like
a
(0)

Report
Copy link

Link copied!

@kalathiya, thanks for the suggestion. still getting same error. Is there any permissions or settings that might cause this? This filter works- sp_city eq 'milledgeville' and sp_coreprovinserteddt ne null

Was this reply helpful? Yes No
Suggested answer

Pstork1 69,556 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

When filtering on dates in an OData filter you definitely don't want the single quotes. But the format of the date value being compared can be very finicky. I find that it is best to format the date and time being used as the standard ISO8601 format rather than a custom format. I tested this query in the list rows using the modifiedon date and it works as expected. As you can see there are no single quotes and the formatDateTime uses the default 'o' format for ISO8601.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
CU04061849-0 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks all, I am using virtual table and that's causing this issue with date filters. regular tables are working as expected. any idea how to filter dates while using virtual tables?

Filter array is not working either. not sure if my syntax is correct. no error but not filtering..

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Given the sp_ in front of the fields is it a virtual table from SharePoint? Or SQL or just a virtual table of some system in Dataverse?

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

I just created a virtual Dataverse table from one of my SharePoint lists. Filtering by date works without an issue. I would double check the column definition in the virtual table to verify that it is a Date column. Also check the actual data and make sure that your filters do what you expect them to.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
CU04061849-0 13 on at

Like
a
(0)

Report
Copy link

Link copied!

@Pstork1 Thanks for helping me with this.
This table is created from an SQL view. Here are the properties and data. we tried both time zone adjustment options..

Was this reply helpful? Yes No