How can I effectively use filterByFormula in Airtable to filter records by a specific date field without defaulting to the first entry?

Other Questions Airtable
1

I’m trying to retrieve a specific record from Airtable using a date filter. In my setup, I have a ‘Name’ field where I store dates, with the field type configured as date. My goal is to filter the records to find the one where the Name equals 8/01/2022.

Unfortunately, I’m facing an issue. Instead of getting the correct record that matches this date, my filter seems to skip it and always returns the very first entry in the table.

This is the code I am currently using:

let dateToCheck = new Date("08/01/2022").toISOString().slice(0, 10);

const airtableInstance = new Airtable({ apiKey: apiKey }).base(baseID);

airtableInstance("tabledata")
  .select({
    filterByFormula: `"DATESTR({Name})='${dateToCheck}'"`,
    view: "Grid view",
  })
  .eachPage(
    function processRecords(records, fetchNext) {
      records.forEach(function (record) {
        let recordDetails = {
          date: record.get("Name"),
          game: record.get("games"),
        };
        saveData(recordDetails);
      });
      try {
        fetchNext();
      } catch {
        return;
      }
    },
    function complete(err) {
      if (err) {
        console.error(err);
        return;
      }
    }
  );

What I receive in return is:

{date: '2022-07-29', game: Array(6)}
date: "2022-07-29"
game: Array(6)

The returned date, ‘2022-07-29’, is from the first entry of my table, which is not what I’m intending to filter for. Even when I double-check my ISO string, it correctly displays 8/01/2022. Can someone help me identify the issue with my filterByFormula syntax?

2

Your date conversion logic is the issue. When you do new Date("08/01/2022").toISOString().slice(0, 10), you get “2022-08-01” but your filterByFormula probably isn’t working because of timezone issues or date formatting problems. I’ve hit this same problem with Airtable date fields. Skip the ISO conversion and slicing - use Airtable’s built-in date functions instead. Try IS_SAME({Name}, '2022-08-01', 'day') in your filterByFormula. This function handles date comparisons and timezone conversions automatically. Also double-check that your Airtable date field actually stores dates as date objects, not text strings that look like dates.

3

Your filterByFormula syntax has extra quotes around the DATESTR function. You’ve got "DATESTR({Name})='${dateToCheck}'" which treats it as a literal string instead of executing the formula. Drop the outer quotes: DATESTR({Name})='${dateToCheck}'. But honestly, I’d switch to DATETIME_FORMAT instead of DATESTR for date comparisons. Had the same issue last month and DATETIME_FORMAT handles timezone differences way better. Try DATETIME_FORMAT({Name}, 'YYYY-MM-DD')='${dateToCheck}' - you’ll get much more consistent results when filtering dates.