Marketing Cloud - API - Data Extension - Filter by Date

Question

I'm retrieving values from a Data Extension using the GET method on https://[YOUR SUBDOMAIN].rest.marketingcloudapis.com/data/v1/customobjectdata/key/[EXTERNAL_KEY]/rowset

without any filter the results are as following:

data_registro is defined as Date in the Data Extension, I'm trying to filter the results by data_registro with no luck:

How should I write the query string in order to filter by date?

Already tried these:

eq '2021-09-13T10:30:00Z'
eq '2021-09-13T10:30:00'
eq '9/13/2021 10:30:00'
eq '9/13/2021 10:30:00 AM'
eq datetime'9/13/2021 10:30:00 AM'
like '9/13/2021 10:30:00 AM'

Answer 1

You were very close, you need to put it in the YYYY-MM-DDTHH:mm:ss format, like you had in your second example. The difference though is that your equals is so specific it likely was not accounting for milliseconds, etc. that would be inside the data but not displayed. Returning a false result.

So you would pass something like:

?$filter=date gte '2020-01-13T00%3a00%3a00'

To get all the entries that have a value in the date field that are before January 13th 2020. I usually use it as a 24 hour clock for the time portion and that seems to work well.

You also can do it without a time (defaults to 00:00:00):

?$filter=entrydate gte '2020-01-13'

I would not recommend eq for dates though, but instead set a 'between' statement. This is because what is stored may have more added to the seconds like milliseconds, etc. that we cannot see or account for and will return false.

Something like this is my recommendation:

?$filter=date gte '2020-01-13T13%3a22%3a43' and date lt '2020-01-13T13%3a22%3a44'

Which would get those that exist within a 1 second timeline up to the next second.