skcah
Writing / Netsuite

SuiteQL Date Filtering: Current Month, Previous Month and Date Ranges

Filter SuiteQL records by the current month, previous month or a custom date range without losing rows at time boundaries.

Ryan Sheehy2 minute read

The safest general-purpose SuiteQL date filter uses an inclusive start and an exclusive end:

WHERE transaction.trandate >= TO_DATE(?, 'YYYY-MM-DD')
  AND transaction.trandate <  TO_DATE(?, 'YYYY-MM-DD')

For July 2026, bind 2026-07-01 and 2026-08-01. This pattern works for both dates and timestamps because it does not assume the final record occurs at 23:59:59.

Filter the current calendar month

Use TRUNC to find the first day of the current month:

WHERE transaction.trandate >= TRUNC(CURRENT_DATE, 'MM')
  AND transaction.trandate <  ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), 1)

The two boundaries mean “from the beginning of this month, up to but not including the beginning of next month.”

Filter the previous calendar month

Move the current-month boundary back by one month:

WHERE transaction.trandate >= ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -1)
  AND transaction.trandate <  TRUNC(CURRENT_DATE, 'MM')

This also handles January correctly because ADD_MONTHS moves into December of the previous year.

Filter a custom range from SuiteScript

Keep the input format explicit and bind values instead of concatenating them into the SQL:

const startDate = '2026-07-01';
const endDate = '2026-08-01';

const rows = query.runSuiteQL({
  query: `
    SELECT
      transaction.id,
      transaction.tranid,
      transaction.trandate
    FROM transaction
    WHERE transaction.trandate >= TO_DATE(?, 'YYYY-MM-DD')
      AND transaction.trandate <  TO_DATE(?, 'YYYY-MM-DD')
  `,
  params: [startDate, endDate],
}).asMappedResults();

Treat endDate as the first excluded date. For a report covering 1–31 July, the end value is 1 August.

Preserve an outer join

If the date belongs to an optional joined record, put the range in the join:

FROM customer
LEFT JOIN transaction
  ON transaction.entity = customer.id
 AND transaction.trandate >= TO_DATE(?, 'YYYY-MM-DD')
 AND transaction.trandate <  TO_DATE(?, 'YYYY-MM-DD')

Putting those conditions in WHERE removes customers with no transaction in the period and effectively changes the result to an inner join.

Avoid these date-range mistakes

  • Do not use BETWEEN with an end date when the field may contain a time.
  • Do not compare a date field with an account-formatted display string.
  • Do not build SQL by inserting user-entered dates into the query text.
  • Do not filter only by month number; July exists in every year.
  • Do not wrap every stored date in TO_CHAR merely to perform a range comparison.