skcah
Writing / Netsuite

SuiteQL Date Bind Parameters: TO_DATE and TO_TIMESTAMP Examples

Pass dates and timestamps safely to N/query SuiteQL using bind parameters, TO_DATE and TO_TIMESTAMP with explicit formats.

Ryan Sheehy2 minute read

When SuiteQL receives a date from SuiteScript, pass a predictable string as a bind parameter and convert it inside the query:

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

This separates data from SQL and documents the format SuiteQL should parse.

Date versus timestamp parameters

Use TO_DATE when only the calendar date matters:

TO_DATE(?, 'YYYY-MM-DD')

Use TO_TIMESTAMP when the time matters:

TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')

Example:

query.runSuiteQL({
  query: `
    SELECT id, systemnote.date
    FROM systemnote
    WHERE systemnote.date >=
          TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')
  `,
  params: ['2026-07-27 09:30:00'],
});

Match the parameter text to the format mask exactly. MM is month; MI is minute.

Do not bind identifiers

Parameters can represent values, but not table names, column names or SQL keywords:

// Valid: the value is bound
query.runSuiteQL({
  query: 'SELECT id FROM transaction WHERE type = ?',
  params: ['SalesOrd'],
});

If a user can choose a sort column, map their choice to a fixed allowlist rather than trying to bind the identifier.

Keep placeholder order stable

Question-mark placeholders are positional:

const params = [];
const conditions = [];

conditions.push(`transaction.trandate >= TO_DATE(?, 'YYYY-MM-DD')`);
params.push(startDate);

conditions.push(`transaction.trandate < TO_DATE(?, 'YYYY-MM-DD')`);
params.push(endDate);

Build each condition and its parameter together. This prevents an optional filter from shifting every later value.

Normalise JavaScript dates first

A JavaScript Date contains a timestamp and time-zone interpretation. Convert it deliberately before using it as a SuiteQL date:

function toIsoDate(date) {
  const year = date.getFullYear();
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  return `${year}-${month}-${day}`;
}

Choose local or UTC getters based on the business rule. The important part is not to let an implicit conversion decide which calendar day is sent.

Common causes of date conversion errors

  • The value does not match the SQL format mask.
  • A timestamp was sent to a date-only expression.
  • Month and minute tokens were confused.
  • A display-formatted NetSuite date was assumed to be ISO.
  • Placeholder order no longer matches the params array.
  • An optional empty string was converted instead of omitting the filter.