skcah
Writing / Netsuite

SuiteQL Time Zones: Why Dates Can Shift by One Day

Diagnose SuiteQL dates that appear one day early or late by separating date-only values, timestamps and JavaScript time-zone conversion.

Ryan Sheehy3 minute read

A SuiteQL date usually appears to shift when a date-only business value is accidentally treated as an instant in time.

For example, midnight in one time zone can be the previous calendar day in another. The SQL may be correct while the conversion performed before or after the query changes the displayed day.

First determine what the field represents

There are two different kinds of values:

  • A date-only value, such as a transaction date, represents a calendar day.
  • A timestamp, such as a system note time, represents an event at a particular time.

Do not apply the same conversion strategy to both.

Return an explicit display value

During diagnosis, return both the source value and an explicitly formatted value:

SELECT
  transaction.id,
  transaction.trandate,
  TO_CHAR(transaction.trandate, 'YYYY-MM-DD') AS trandate_text
FROM transaction

If trandate_text is correct but the application displays another day, the shift is probably happening after SuiteQL returns the result.

Avoid parsing a date-only string as UTC

This JavaScript expression gives the text a time-zone meaning:

const date = new Date('2026-07-27');

If the business rule is simply “27 July,” keep the result as 2026-07-27 or parse its year, month and day deliberately. Do not convert it through a JavaScript Date unless the code genuinely needs an instant.

Use bounded ranges for timestamps

For records occurring during a local business day, calculate the intended boundaries deliberately and query an inclusive start with an exclusive end:

WHERE systemnote.date >= TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')
  AND systemnote.date <  TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS')

The calling code must decide which time zone those boundary strings represent. Document that decision beside the conversion.

Check session-relative values

Functions such as CURRENT_DATE are session-relative. Near midnight, the session date can differ from a UTC-derived date in an integration.

When a scheduled or integration script must use a particular business date, consider passing that date explicitly rather than allowing separate systems to calculate “today.”

A practical debugging checklist

  1. Return the raw field and TO_CHAR(..., 'YYYY-MM-DD').
  2. Log the value immediately after asMappedResults().
  3. Log the value again after application conversion.
  4. Identify whether the source is a date or timestamp.
  5. Record the account, user and integration time-zone assumptions.
  6. Test records close to midnight and daylight-saving changes.
  7. Keep date-only values as date-only strings where possible.