How to use filterByFormula with a date field

[Alessandro_Mari]

Hi folks,
I’m using filterByFormula param to filter records of a table but I’m able to select for a specific date.

Below and example:

request:
https://api.airtable.com/v0/appFOOBAR/Spese?filterByFormula=AND({Importo}=111.22,{Data}='2022-04-01')

response:

{
	"records": []
}

while with the request below I got a result:

https://api.airtable.com/v0/appFOOBAR/Spese?filterByFormula=AND({Importo}=111.22,not({Data}='2022-04-01'))

response:

{
	"records": [
		{
			"id": "recXXXX",
			"createdTime": "2022-05-24T09:38:50.000Z",
			"fields": {
				"Tipologia": "Spesa",
				"Data": "2022-04-01",
				"Importo": 111.22,
				"Name": "Prova 1"
			}
		}
	]
}

so the example using not operator shows that there’s a record with Data=2022-04-01, I don’t understand why this record isn’t extracted using the first request.

[kuovonne · Accepted solution]

Welcome to the Airtable community!

Alessandro_Marino:

I’m using filterByFormula param to filter records of a table but I’m able to select for a specific date.

Is {data} a date field? If so, your formula is comparing a date with a text string, and that just doesn’t work in Airtable formulas. It doesn’t matter that the date looks like a text string in the API response, when the formula is evaluated, Airtable treats the date like an object, and not a string, and dates and objects cannot be equivalent. This is why the api returns the record when you ask for records that are not equivalent.

The easiest fix is to use a formula that turns the date field into a string.

DATESTR({Data})='2022-04-01'

You could also convert the string into a date and then use a date comparison function, but that is more complex.

[Alessandro_Mari]

Thank you @kuovonne !
I was able to fix the formula by following your suggestion!